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

Rick wrote:
> 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.

You upgraded from Cretaceous to Neolithic? Are you sure it is 9.0.2 and not 9.2.0?

My guess is that this is one for metalink and a TAR. Based on what you've written though you've upgraded from long unsupported to more recently unsupported.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)