Re: ORA-03117 resulting from package procedure parameter record types
- From: "Rick" <richard.kromkamp@xxxxxxxxxx>
- Date: 1 Dec 2005 06:59:00 -0800
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@xxxxxxxxxxxxxxxx
> (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).
.
- Follow-Ups:
- References:
- ORA-03117 resulting from package procedure parameter record types
- From: Rick
- Re: ORA-03117 resulting from package procedure parameter record types
- From: DA Morgan
- Re: ORA-03117 resulting from package procedure parameter record types
- From: Rick
- Re: ORA-03117 resulting from package procedure parameter record types
- From: DA Morgan
- ORA-03117 resulting from package procedure parameter record types
- Prev by Date: Re: Oracle Data Miner (ODM)
- Next by Date: Re: Oracle can read file not allowed by oracle user
- Previous by thread: Re: ORA-03117 resulting from package procedure parameter record types
- Next by thread: Re: ORA-03117 resulting from package procedure parameter record types
- Index(es):
Relevant Pages
|
