RE: ORA-04030

From: Fermin Bernaus Berraondo <fbernaus_at_sammic.com>
Date: Thu, 24 Apr 2003 03:32:08 -0800
Message-ID: <F001.0058852D.20030424033208@fatcity.com>

        Why not split the loop and do more frecuent commits, it looks like it needs too much memory to create such a big array.

Fermin.

-----Mensaje original-----
De: root_at_fatcity.com [mailto:root_at_fatcity.com]En nombre de John Dunn Enviado el: jueves, 24 de abril de 2003 11:57 Para: Multiple recipients of list ORACLE-L Asunto: ORA-04030

We get the error :
ORA-04030 out of process memory when trying to allocate 16396 bytes (koh-kghu sessi, kol vstring)

ORA-06512: at "SYS.DBMS_SYS.SQL", line 917 
ORA-06512: at "SYS.DBMS_SQL", line 120 
ORA-06512: at line 194 

Version is 8.1.7.0.0
Any one any advice on avoiding this error? The code being run is similar to the following:

declare

        jmax number;

	arrJOB_ID           dbms_sql.number_table;
	arrOUTPUT_FILE      dbms_sql.number_table;
	arrMAILPIECE_NUMBER dbms_sql.number_table;
	arrREFERENCE        dbms_sql.varchar2_table;
	arrADDRESS_1        dbms_sql.varchar2_table;
	arrDATA_1           dbms_sql.varchar2_table;
	arrDATA_2           dbms_sql.varchar2_table;
	arrDATA_3           dbms_sql.varchar2_table;
	arrDATA_4           dbms_sql.varchar2_table;
	arrDATA_5           dbms_sql.varchar2_table;
	arrDATA_6           dbms_sql.varchar2_table;
	arrDATA_7           dbms_sql.varchar2_table;
	arrDATA_8           dbms_sql.varchar2_table;
	arrDATA_9           dbms_sql.varchar2_table;

    stmnt VARCHAR2(1000);
    c NUMBER;
    dummy NUMBER;
    i number;

begin

    i := 35252;
    jmax := 100000;

        for j in 1..jmax loop

			arrJOB_ID(j)           := i;
			arrOUTPUT_FILE(j)      := 1;
			arrMAILPIECE_NUMBER(j) := j;
			arrREFERENCE(j)        := 'REF'        ||i||j;
			arrADDRESS_1(j)        := 'Address '   ||i||j;
			arrDATA_1(j)           := '1some data' ||i||j;
			arrDATA_2(j)           := '1more data' ||i||j;
			arrDATA_3(j)           := '1extra data'||i||j;
			arrDATA_4(j)           := '2some data' ||i||j;
			arrDATA_5(j)           := '2more data' ||i||j;
			arrDATA_6(j)           := '2extra data'||i||j;
			arrDATA_7(j)           := '3some data' ||i||j;
			arrDATA_8(j)           := '3more data' ||i||j;
			arrDATA_9(j)           := '3extra data'||i||j;
     
        end loop;
        
        stmnt := 'insert into 
        BIG
            (JOB_ID,
             OUTPUT_FILE,
             MAILPIECE_NUMBER,
             REFERENCE,
             ADDRESS_1,
             DATA_1,
             DATA_2,
             DATA_3,
             DATA_4,
             DATA_5,
             DATA_6,
             DATA_7,
             DATA_8,
             DATA_9)
        values
            (:arrJOB_ID,


             :arrOUTPUT_FILE,

             :arrMAILPIECE_NUMBER,

             :arrREFERENCE,

             :arrADDRESS_1,

             :arrDATA_1,

             :arrDATA_2,

             :arrDATA_3,

             :arrDATA_4,

             :arrDATA_5,

             :arrDATA_6,

             :arrDATA_7,

             :arrDATA_8,

             :arrDATA_9)';


        c := dbms_sql.open_cursor;
        
        dbms_sql.parse(c, stmnt, dbms_sql.native);
        dbms_sql.bind_array(c, ':arrJOB_ID', arrJOB_ID);
        dbms_sql.bind_array(c, ':arrOUTPUT_FILE', arrOUTPUT_FILE);
        dbms_sql.bind_array(c, ':arrMAILPIECE_NUMBER', arrMAILPIECE_NUMBER);
        dbms_sql.bind_array(c, ':arrREFERENCE', arrREFERENCE);
        dbms_sql.bind_array(c, ':arrADDRESS_1', arrADDRESS_1);
        dbms_sql.bind_array(c, ':arrDATA_1', arrDATA_1);
        dbms_sql.bind_array(c, ':arrDATA_2', arrDATA_2);
        dbms_sql.bind_array(c, ':arrDATA_3', arrDATA_3);
        dbms_sql.bind_array(c, ':arrDATA_4', arrDATA_4);
        dbms_sql.bind_array(c, ':arrDATA_5', arrDATA_5);
        dbms_sql.bind_array(c, ':arrDATA_6', arrDATA_6);
        dbms_sql.bind_array(c, ':arrDATA_7', arrDATA_7);
        dbms_sql.bind_array(c, ':arrDATA_8', arrDATA_8);
        dbms_sql.bind_array(c, ':arrDATA_9', arrDATA_9);
        
        dummy := dbms_sql.execute(c);
        dbms_sql.close_cursor(c);

        commit;

end;

/

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: John Dunn
  INET: john.dunn_at_sefas.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Fermin Bernaus Berraondo
  INET: fbernaus_at_sammic.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Thu Apr 24 2003 - 06:32:08 CDT