| 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: a simple cursor gives me PLS-00801: internal error [74303]
I opened bug #480447 for this error. The workaround I found is to:
> cursor testCur is
> select distinct TEST_REGISTRY.TEST_ID, level
> from TEST_REGISTRY
> connect by prior PARENT_ID=TEST_REGISTRY.TEST_ID
> start with (PARENT_ID is null)
> order by 2;
^^^^^
Do the order by positionally instead of by name. Using level in the order by must be confusing the pl/sql parser for some reason.
If you wish to track this with support, open a tar and have them reference the above bug number. I reproduced internally on 7.3.3 on solaris so it looks like a generic bug.
On Thu, 17 Apr 1997 18:44:04 -0400, James Sherring <james.sherring_at_citicorp.com> wrote:
>I am getting a strange PL/SQL error mesage
>(PLS-00801: internal error [74303])
>when compiling a simple cursor under Oracle 7.3 on NT4.
>The same SQL works fine if I execute it from SQL*Plus.
>
>The problematic cursor definition is
>/*
>**********************************************************************
>*/
> cursor testCur is
> select distinct TEST_REGISTRY.TEST_ID, level
> from TEST_REGISTRY
> connect by prior PARENT_ID=TEST_REGISTRY.TEST_ID
> start with (PARENT_ID is null)
> order by level;
>/*
>**********************************************************************
>*/
>This is a trivial version of the associated table:
>/*
>**********************************************************************
>*/
>create table TEST_REGISTRY (
> TEST_ID number(10),
> TEST_NAME varchar2(100),
> PARENT_ID number(10),
> constraint TEST_REGISTRY_KEY primary key (TEST_ID))/
>insert into TEST_REGISTRY values (1,'Citicorp', null)/
>insert into TEST_REGISTRY values (3,'IPD', 1)/
>insert into TEST_REGISTRY values (2,'CGAM', 3)/
>/*
>**********************************************************************
>*/
>The following SQL from SQL*Plus gives the expected output:
>/*
>**********************************************************************
>*/
>SQL> select distinct TEST_REGISTRY.TEST_ID, level
> 2 from TEST_REGISTRY
> 3 connect by prior PARENT_ID=TEST_REGISTRY.TEST_ID
> 4 start with (PARENT_ID is null)
> 5 order by level;
>
> TEST_ID LEVEL
>--------- ---------
> 1 1
>
>SQL>
>/*
>**********************************************************************
>*/
>But if I try the same code in a cursor...
>/*
>**********************************************************************
>*/
>create or replace procedure test is
> cursor testCur is
> select distinct TEST_REGISTRY.TEST_ID, level
> from TEST_REGISTRY
> connect by prior PARENT_ID=TEST_REGISTRY.TEST_ID
> start with (PARENT_ID is null)
> order by level;
>begin
> null;
>end test;
>/
>show errors;
>
>Warning: Procedure created with compilation errors.
>
>Errors for PROCEDURE TEST:
>
>LINE/COL ERROR
>--------
>-----------------------------------------------------------------
>0/0 PLS-00801: internal error [74303]
>
>/*
>**********************************************************************
>*/
>Oracle documentation reveals:
>
>> PLS–00801 internal error [num]
>> Cause This is a generic internal error that might occur during
>> compilation or execution. The first parameter is the internal error number.
>> Action Report this error as a bug to your Customer Support representative.
>
>If anyone can see something obvious that I am missing, I would greatly
>appreciate the pointer.
>The 'order by level' clause seems to be key.
>
>I have tried this on several boxes, each running:
>>Oracle7 Server Release 7.3.2.2.0 - Production Release
>>With the distributed, replication and parallel query options
>>PL/SQL Release 2.3.2.2.0 - Production
and
>>SQL*Plus: Release 3.3.2.0.2
>runing on NT4
>
>Thanks,
>James Sherring
>james.sherring_at_citicorp.com
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |