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

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

>

Sorry, I guess I'm just accustomed to doing everything visually nowadays. Here is the DDL for the tables. I'm sure that the PK's are not required, but I threw them in there anyway.

CREATE TABLE PROG_TBL (PROG_ID NUMBER NOT NULL); ALTER TABLE PROG_TBL ADD CONSTRAINT PROG_PK PRIMARY KEY (PROG_ID); CREATE TABLE CA_TBL (CA_ID NUMBER NOT NULL); ALTER TABLE CA_TBL ADD CONSTRAINT CA_PK PRIMARY KEY (CA_ID); Thanks for the assistance. Received on Thu Dec 01 2005 - 10:48:27 CST