Malloc errors in stored procedure - Reposted

From: Raghu Ram <Raghu=Ram%Supplier%IM=Hou_at_bangate.compaq.com>
Date: Wed, 10 Aug 1994 19:06:25 GMT
Message-ID: <940810140625_at_raghu.im.hou.compaq.com>

We are experiencing the following errors quite INCONSISTENTLY. Any pointers to help in troubleshooting or having a workaround would be appreciated.

Environment :

Sco Unix System V/386 Release 3.2

ORACLE7 Server Release 7.0.15.4.0 - Production With the procedural and distributed options PL/SQL Release 2.0.17.1.0 - Production
SQL*Plus: Release 3.1.2.3.1

Salient features of program - Stored Procedure : ...
...
pl_reg_nbr registrant.reg_nbr%type;.. ....
cursor outer_regt_curs is

        select reg_nbr from registrant;

cursor sel_detail is

        select  country_code, customer_id, intro_date, expire_date,
        first_name, surname, name_prefix, name_suffix, job_cat, job_title,
        max_site_emp, min_site_emp,transaction_datetime,transaction_type
          from    registrant
        where   reg_nbr = pl_reg_nbr;...
                    ..
                    ..
                    begin
                    ...
                    for reg in outer_regt_curs LOOP
                        pl_reg_nbr := reg.reg_nbr;

        FOR det in sel_detail  LOOP


                ....

                ....<< calls various procedures, which only check


                       data and insert



                ....

                ....

                 END LOOP;
         ....
         END LOOP;

Error generated :

ERROR:
ORA-04030: out of process memory when trying to allocate 32428 bytes (pga heap,ksm stack)
ORA-07324: smpall: malloc error while allocating pga. AT&T System V/386 Error: 12: Not enough space

begin xtrac_regt_spo; end;
 *
 ERROR at line 1:

 ORA-04030: out of process memory when trying to allocate 9520 bytes (PLS
 non-lib hp,PL/SQL STACK)
 ORA-07324: smpall: malloc error while allocating pga.

 AT&T System V/386 Error: 12: Not enough space

Alternatives Tried (which all failed):

1. Replaced the FOR LOOP with a WHILE ( and OPEN/FETCH/CLOSE) LOOP.
2. Added / removed indexes.
3. Removed procedures that are being called by the body of the program.
4. Added just one field (reg_nbr) to be selected by the outer cursor.
5. Doubled the memory available by each process running under UNIX. This

resulted in the `pga' being allowed to increase to 48M before the error was generated.

The result of trying all the above was the same for all - The process just allowed different quantities of data (number of rows from registrant) to be processed before it failed.

Thanks,
R.Raghu Ram
(713) 378 0487 Received on Wed Aug 10 1994 - 21:06:25 CEST