a simple cursor gives me PLS-00801: internal error [74303]

From: James Sherring <james.sherring_at_citicorp.com>
Date: 1997/04/17
Message-ID: <3356A7B4.3F45@citicorp.com>#1/1

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 Received on Thu Apr 17 1997 - 00:00:00 CDT