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)
Received on Thu Dec 01 2005 - 10:23:02 CST
