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 <ricky_ray99_at_hotmail.com>
Date: 5 Dec 2005 08:58:16 -0800
Message-ID: <1133801896.200519.179450@g14g2000cwa.googlegroups.com>

Terry Dykstra wrote:
> How are you calling this sp? Using dynamic SQL and if so, which format.
> Seeing some PB code would help.
>
> --
> Terry Dykstra
> Canadian Forest Oil Ltd.
>
>
> "Rick" <richard.kromkamp_at_contax.com> wrote in message
> news:1133472044.923159.56690_at_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
> >

Terry, I appreciate your help, but for now, I don't want to get bogged down in PB code, as we have been able to determine that the problem is definately an Oracle issue, and I think that getting into PB code would just further complicate the issue. I managed to find a work around in one of my tools (PL/SQL Developer) by forcing OCI7 mode on OCI8, but this was an option specific to that tool, and is not an available option for any other tools, like Powerbuilder, and my DBA's tell me that we definately cannot use OCI7 when going to production.

We've opened a TAR with Oracle, and I will provide any helpful updates here as they come in. In the mean time, we have found a work around by creating interface packages for any of the problem packages, and then changing the calling code to point to the interface package. It's not the best solution; in essence, we have just swept the problem under the carpet (hopefully it doesn't come back to haunt us in the future as these things have a tendency to do). Hopefully the TAR will provide some assistance, but our DBA's don't seem too optimistic. After having some associates test the problem on their servers, it sounds like this is a common problem in Oracle 8i and 9i, but has since been resolved in Oracle 10g. My guess is that Oracle's solution will be to upgrade to Oracle 10g. Received on Mon Dec 05 2005 - 10:58:16 CST

Original text of this message

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