ORA-03117 resulting from package procedure parameter record types

[Rick]

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.

Any help would be appreciated.

[DA Morgan]

You upgraded from Cretaceous to Neolithic? Are you sure it is 9.0.2
and not 9.2.0?

My guess is that this is one for metalink and a TAR. Based on what
you've written though you've upgraded from long unsupported to more
recently unsupported.
--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)

[Rick]

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.

[DA Morgan]

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.

[Rick]

Here is the original message for those who may require it:
-----------------------------------------------------------------------

would be as follows:

-----------------------------------------------------------------------

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).

[DA Morgan]

And again ... it does not include the CREATE TABLE statements.

[Rick]

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.

[DA Morgan]

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.

[Rick]

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.

[DA Morgan]

Rick wrote:

> 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.

You mean this this?

SQL> desc test_pkg
PROCEDURE PROC1
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
IREC_X RECORD IN
CA_ID NUMBER IN
PROG_REC RECORD IN
PROG_REC RECORD IN
PROG_ID NUMBER IN

SQL>

;-)

[Rick]

If I could see that, I would be a happy camper. In one of my tools
(PL/SQL Developer), I've found a way around the issue, but specifying
the "Force OCI7 mode on OCI8" connection parameter. Unfortunately, I
can't specify anything like that in PB, so it's pretty much back to
square one. Sounds like it's time to let the DBA's take over.

Thanks for all your help.

[Rob Zijlstra]

On Thu, 01 Dec 2005 11:11:34 -0800, DA Morgan <damo...@psoug.org>
wrote:

>Rick wrote:
>
>> 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.
>
>You mean this this?
>
>SQL> desc test_pkg
>PROCEDURE PROC1
> Argument Name Type In/Out Default?
> ------------------------------ ----------------------- ------ --------
> IREC_X RECORD IN
> CA_ID NUMBER IN
> PROG_REC RECORD IN
> PROG_REC RECORD IN
> PROG_ID NUMBER IN
>
>SQL>
>
>;-)

Justr tested this:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> describe test_pkg

PROCEDURE PROC1
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
IREC_X RECORD IN
CA_ID NUMBER IN
PROG_REC RECORD IN
PROG_REC RECORD IN
PROG_ID NUMBER IN

Best Regards

Rob Zijlstra

[Turkbear]

"Rick" <richard....@contax.com> wrote:

>If I could see that, I would be a happy camper. In one of my tools
>(PL/SQL Developer), I've found a way around the issue, but specifying
>the "Force OCI7 mode on OCI8" connection parameter. Unfortunately, I
>can't specify anything like that in PB, so it's pretty much back to
>square one. Sounds like it's time to let the DBA's take over.
>
>Thanks for all your help.

In fact, have them ( as the error message details in the Docs indicate) contact Support ( That's why they get the big bucks)
----------------------------------------------------------------------------------------------------------------------

ORA-03117 two-task save area overflow

Cause: This is an internal error message not usually issued.

Action: Contact Oracle Support Services.

-------------------------------------------------------------------------------------------------------------------------

[Terry Dykstra]

What version of PB are you using and which driver (O73, O84, O90, OR8 ..)

--
Terry Dykstra
Canadian Forest Oil Ltd.

"Rick" <richard....@contax.com> wrote in message
news:1133458278....@g49g2000cwa.googlegroups.com...

[Rick]

PB Version: 9.0.2 (Build 7509)
DB Driver: O90 Oracle 9.0.1

[Terry Dykstra]

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....@contax.com> wrote in message

news:1133472044....@g47g2000cwa.googlegroups.com...

[Rick]

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.

[Rick]

Our DBA's have informed me that as expected, Oracle's response was to
upgrade to Oracle 10g. Their workaround was to run the code from the
server, which is impossible, and makes no sense at all. Unfortunately,
our DBA's have chosen not to push the matter any further since we have
a workaround in place as described in the earlier responses.