Re: problems creating script of insert statements

From: Anand <ianand_at_indiainfo.com>
Date: 17 Jan 2002 22:00:17 -0800
Message-ID: <29d14839.0201172200.5e188074_at_posting.google.com>


Hi Joe,
I think you can do that without PL/SQL.
Just Replace "commit" in your Insert statement with "'||chr(10)||'commit'" This shall do the trick. Chr(10) is the carriage return character

joemellors_at_aol.com (Joe Mellors) wrote in message news:<53566469.0201161633.49a6f0d8_at_posting.google.com>...
> 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 || ''');
> COMMIT;'
> 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 = 'EHFTSC';
>
> spool off
>
> 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';
> OUTFILE UTL_FILE.FILE_TYPE;
> BEGIN
> OUTFILE := UTL_FILE.FOPEN('C:/JOE/SQL', 'PROJIPPN.txt', 'W');
> FOR ROW IN IPP_PARTS_CUR LOOP
> 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);
> END LOOP;
> UTL_FILE.FCLOSE(OUTFILE);
> EXCEPTION
> 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 Fri Jan 18 2002 - 07:00:17 CET

Original text of this message