Re: ORA-03117 resulting from package procedure parameter record types

From: Rick <richard.kromkamp_at_contax.com>
Date: 1 Dec 2005 06:59:00 -0800
Message-ID: <1133449140.339875.109180@z14g2000cwz.googlegroups.com>

DA Morgan wrote:
> Rick wrote:
> > I stand corrected. It is 9.2.0. Our DBA's are in the process of
> > following up with Oracle, although my experience is that that can take
> > some time. I was just hoping to get an early jump on the problem if
> > possible.
> >
> > Yes, we did jump from Cretaceous to Neolithic.
>
> Tried to use what you provided to duplicate the error but you did
> not include the DDL for the tables. I'd be interestedin seeing
> what happens in 10.2.
>
> PS: Repost all as most people won't have access to the older posting.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

Here is the original message for those who may require it:

---

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.

---

As far as I know, the table definitions do not matter, they could be defined with one column of type number (eg. prog_tbl (prog_id number); ca_tbl(ca_id number);. I don't think that this makes much difference, but we have also determined that we are unable to perform a describe on these package procedures in SQL Plus. In fact, I use a development tool that lets me autmatically drill down into the package, to get the procedures, and eventually down to the parameter list, and I get the same error message when I attempt to drill down to the parameters (I assume that the program is issuing a describe to get the parameter list). Received on Thu Dec 01 2005 - 08:59:00 CST