Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-03117 resulting from package procedure parameter record types

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

From: Rick <richard.kromkamp_at_contax.com>
Date: 1 Dec 2005 13:20:44 -0800
Message-ID: <1133472044.923159.56690@g47g2000cwa.googlegroups.com>

Terry Dykstra wrote:
> What version of PB are you using and which driver (O73, O84, O90, OR8 ..)
>
> --
> Terry Dykstra
> Canadian Forest Oil Ltd.
>
>
> "Rick" <richard.kromkamp_at_contax.com> wrote in message
> news:1133458278.892271.69180_at_g49g2000cwa.googlegroups.com...
> >
> > DA Morgan wrote:
> > > Rick wrote:
> > > > 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).
> > > >>
> > > >>And again ... it does not include the CREATE TABLE statements.
> > > >>--
> > > >>Daniel A. Morgan
> > > >>http://www.psoug.org
> > > >>damorgan_at_x.washington.edu
> > > >>(replace x with u to respond)
> > > >
> > > >
> > > > 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.
> > >
> > > Better. And how about the block you are using to execute
> test_pkg.proc1(?);
> > >
> > > It would help to see how you are loading irec_x.
> > >
> > > Thanks.
> > > --
> > > Daniel A. Morgan
> > > http://www.psoug.org
> > > damorgan_at_x.washington.edu
> > > (replace x with u to respond)
> >
> > The simplest way to determine if the problem is occurring is to do the
> > following in SQL Plus:
> > SQL> describe test_pkg
> > ERROR:
> > ORA-03117: two-task save area overflow
> >
> > If I create a test block (without bind variables) it will actually
> > execute no problem. The problem occurs when attempting to execute the
> > procedure from Powerbuilder, or PL/SQL Developer or any other client
> > tool that we use. Regardless, if you can do the describe, than it is
> > suffice to say that you do not have the problem.
> >

PB Version: 9.0.2 (Build 7509)
DB Driver: O90 Oracle 9.0.1 Received on Thu Dec 01 2005 - 15:20:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US