ORA-03117 resulting from package procedure parameter record types

From: Rick <richard.kromkamp_at_contax.com>
Date: 30 Nov 2005 08:22:58 -0800
Message-ID: <1133367778.368246.240090@g44g2000cwa.googlegroups.com>

I am at a client who has recently migrated from Oracle 7.3.4 to Oracle 9.0.2. During the migration, we noticed that we could not execute a number of the package procedures, resulting in an error message ORA-03117 (internal message that should not occur). After some investigation, we have determined that the problem only occurs for packages that contain procedures with parameters of type record, where the record contains a reference to a tables ROWTYPE. An example of this would be as follows:

create or replace package test_pkg is

    TYPE y_recTyp IS RECORD
(

      PROG_REC prog_tbl%rowtype
    );

    TYPE x_recTyp IS RECORD
(

       CA_ID                   CA_TBL.CA_ID%TYPE
      ,PROG_REC          y_rectyp

    );

    procedure proc1 (irec_x in x_recTyp); end test_pkg;

create or replace package body test_pkg is

    procedure proc1 (irec_x in x_recTyp) is     begin

        null;
    end proc1;
end test_pkg;

In this example, attempting to call package procedure proc1 specifying bind variables fails with the oracle error message "ORA-03117: two task save area overflow". We have determined that we can get around this by not using the ROWTYPE reference in the first record, and instead declaring a third record type referencing each of the specific columns, and then referring to this record type as opposed to using the ROWTYPE. This is not the preferred solution however.

Any help would be appreciated. Received on Wed Nov 30 2005 - 10:22:58 CST