| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: ORA-03117 resulting from package procedure parameter record types
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.
>
>
>
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 - 11:31:18 CST
 |
 |