problems creating script of insert statements

I am trying to create a script containing a large number of insert statements.
Because of limited disk space for roll back segments etc I first attempted the following (problem being I want to add commits to avoid roll back segment errors):

SET PAGESIZE 0
SET HEADING OFF
SET FEEDBACK OFF
SET LINESIZE 1000          SPOOL C:\JOE\SQL\projippn.sql;

SELECT DISTINCT 'INSERT INTO APACR.TBLPROJIPPNTABLE VALUES (''' ||
A.PROJID || ''', ''' || A.IPP || ''', ''' || A.PNR || ''', ''' ||
A.MFC || ''', ''' || C.SEQNO || ''', ''' || C.PICKUPMKEY || ''');

AND A.MFC = C.MFC
AND A.PROJID = C.PROJID
AND C.CHG = 'R'
AND A.PROJID = 'EHFTSC';

SET LINESIZE 80
SET HEADING ON
SET PAGESIZE 24
SET FEEDBACK ON which didn't work because a carriage return is needed before the commit and I didn't know how to take care of that. I have since tried writing to a file using dbms_output within a cursor's loop but the buffer's max is 1000000 bytes and the file will be way in excess of this (around 2 Gig I think)

so my most recent attempt has been to do something similar to what I tried with dbms_output but with utl_file. I first entered get utl_file_dir = * in the init.ora file, then tried the following :

DECLARE
CURSOR IPP_PARTS_CUR IS

	SELECT DISTINCT A.PROJID, A.IPP, A.PNR, A.MFC, C.SEQNO, C.PICKUPMKEY
	FROM
		APACR.TBLLOCATIONS A, APACR.STBLPROJECTPARTS C
	WHERE A.PNR = C.PNR
	AND A.MFC = C.MFC
	AND A.PROJID = C.PROJID
	AND C.CHG = 'R'
	AND A.PROJID = 'EHUKR';

	UTL_FILE.PUT_LINE(OUTFILE, 'INSERT INTO APACR.TBLPROJIPPNTABLE VALUES
(''' || ROW.PROJID || ''', ''' || ROW.IPP || ''', ''' || ROW.PNR ||
''', ''' || ROW.MFC || ''', ''' || ROW.SEQNO || ''', ''' ||
ROW.PICKUPMKEY || ''');');
      	UTL_FILE.NEW_LINE(OUTFILE);
      	UTL_FILE.PUT(OUTFILE, 'COMMIT;');
      	UTL_FILE.NEW_LINE(OUTFILE);

	WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE('no_data_found');
                UTL_FILE.FCLOSE(OUTFILE);
        WHEN UTL_FILE.INVALID_PATH THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.INVALID_PATH');
                UTL_FILE.FCLOSE(OUTFILE);
            WHEN UTL_FILE.READ_ERROR THEN
                DBMS_OUTPUT.PUT_LINE(' UTL_FILE.READ_ERROR');
                UTL_FILE.FCLOSE(OUTFILE);
            WHEN UTL_FILE.WRITE_ERROR THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
                UTL_FILE.FCLOSE(OUTFILE);
            WHEN UTL_FILE.INVALID_MODE THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
                UTL_FILE.FCLOSE(OUTFILE);
            WHEN UTL_FILE.INVALID_OPERATION THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
             UTL_FILE.FCLOSE(OUTFILE);
            WHEN UTL_FILE.INTERNAL_ERROR THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
                UTL_FILE.FCLOSE(OUTFILE);
            WHEN UTL_FILE.INVALID_FILEHANDLE THEN
                DBMS_OUTPUT.PUT_LINE('UTL_FILE.WRITE_ERROR');
                UTL_FILE.FCLOSE(OUTFILE);
            WHEN OTHERS THEN
                DBMS_OUTPUT.PUT_LINE('other stuff');
                UTL_FILE.FCLOSE(OUTFILE);
 

END;
/

which I nicked parts of from this newsgroup. When I run this it says that the procedure was successful but no file is created.

Any help with this or the first possible solution would be greatly appreciated.

Cheers,
Joe Mellors Received on Thu Jan 17 2002 - 01:33:59 CET