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.
>
Received on Thu Dec 01 2005 - 14:58:07 CST
