11g: Bulk Insert Example

I happen to work recently on a scenario where I had to inset close to 46 Million records . I had to perform a BULK COLLECT (Insert) script so that I dont get ORA-01555: Snapshot too old error.

DECLARE
cursor c1 is
SELECT *
FROM DS.TIME_TEMP_TBLE;

TYPE TYPE_TB1 IS TABLE OF DS.TIME_TARGET_TBLE%ROWTYPE;
TARG_TB1 TYPE_TB1;

l_limit number := 1000000;
BEGIN

OPEN c1;
LOOP
                Fetch c1 BULK COLLECT INTO TARG_TB1 LIMIT l_limit;

                FORALL i IN 1..TARG_TB1.COUNT
                insert into DS.TIME_TARGET_TBLE
                values TARG_TB1(i);
 COMMIT;
        EXIT WHEN c1%ROWCOUNT < l_limit;
END LOOP;
CLOSE c1;
END;
/
COMMIT;
EXIT;

Other different options are to use FOR instead of  FORALL, or use

FORALL i IN TARG_TB1.first..TARG_TB1.last 

From my testing, I felt that using COUNT was faster.

R12.x - ORA-20100: Error: FND_FILE failure. Unable to create file, lxxxxxxx.tmp in the directory, /yyy/zzzz/abc

Concurrent Request Set and Progam results in the following error:

ORA-20100: Error: FND_FILE failure. Unable to create file, lxxxxxx.tmp in the directory, /yyy/zzzz/abc

Oracle Applications produces temporary PL/SQL output files used in concurrent processing. These files are written to a location on the database server node specified by the APPLPTMP environment setting. As the temporary files placed in this directory may contain context sensitive information, it should be a secure directory on the database server node with read and write access for the database server owner. In a multi-node system, the directory defined by APPLPTMP does not need to exist on the application tier servers. In my environment, APPLPTMP= /u03/sqlcom/outbound.

There are many scenarios for this error and I will try to visit most of the cases here.

1. Check the location for APPLPTMP  and confirm that is not 100% full in storage.

2. Set APPLTMP to the first directory in utl_file_dir parameter as FND_FILE writes to the first directory in utl_file_dir.

 SELECT value FROM  v$parameter WHERE name = 'utl_file_dir';

3.  Database Read/Write Check :  If you can write a file using UTL_FILE, which is used by FND_FILE.

set serveroutput on 
DECLARE 
  file_location VARCHAR2(256) := '<first entry on utl_file_dir>'; 
  file_name VARCHAR2(256) := 'utlfile_TEST1.lst'; 
  file_text VARCHAR2(256) := 'THIS IS A TEST'; 
  file_id UTL_FILE.file_type; 
BEGIN 
  file_id := UTL_FILE.fopen(file_Location, file_name, 'W'); 
  UTL_FILE.put_line(file_id, file_text); 
  UTL_FILE.fclose(file_id); 
EXCEPTION 
  WHEN UTL_FILE.INVALID_PATH 
  THEN dbms_output.put_line('Invalid path ' || SQLERRM); 
    WHEN OTHERS 
  THEN dbms_output.put_line('Others '|| SQLCODE || ' ' || SQLERRM); 
END; 
/

If this fails, the issue is on database.

4. Check if you can write a file directly using FND_FILE, which is the package used by the Application. From sqlplus, connected as the apps user, run:

exec FND_FILE.PUT_LINE(FND_FILE.LOG, 'THIS IS A TEST');

If this test works, it would indicate that FND_FILE is working fin.

5. Make sure the file that the concurrent program is trying to create already exists on the APPLPTMP location. Also please verify the permissions on the APPLPTMP directory.

6. If still having problems, your SPFILE may be corrupt or just restart the database. 

 - Shutdown all the Application Services

 - Verify the spfile for the utl_file_dir parameter

 - Restart the database

7. If you decide to change the location of APPLPTMP, Make sure to run autoconfig after changing the APPLPTMP value in the  autoconfig context file.

Reference:

Concurrent Processing - Troubleshooting Concurrent Request ORA-20100 errors in the request logs (Doc ID 261693.1)