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