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
>
Received on Mon Dec 05 2005 - 09:34:09 CST
