Manipulating Local Collections (using TABLE and CAST) 1

[michal]

I am trying to work with the examples from

http://oradoc.photo.net/ora81/DOC/server.815/a67842/04_colls.htm

(search for Manipulating Local Collections )
Since I do not have permission to create a new type I tried to use a "simplified" version namely

DECLARE

    TYPE CourseList IS TABLE OF VARCHAR(20);
   
    courses CourseList := CourseList('abc','def','ghi','opp');
    num_rows INTEGER;

BEGIN
       DBMS_OUTPUT.PUT_LINE('Number of records in table:' || courses.COUNT);  -- prints number of records

    SELECT COUNT(*) INTO num_rows
    FROM TABLE(CAST(courses AS CourseList)) AS new;
    DBMS_OUTPUT.PUT_LINE(num_rows);

END;

Unfortunately when running the above, after successfully printing the number of rows in the table I get the following error:


ERROR at line 1:
ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [],
ORA-06544: PL/SQL: internal error, arguments: [pfrrun.cfrbnd1()], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [0]


Do I have to store objects in my nested table in order to take advantage of the local manipulation? Is something else wrong with my syntax here?
I am using Oracle 8.1.5.

Appereciate any help you can provide,
Thanks!
Michal

 

[karluk]

This issue is way out of my league, but I did find a reference to it on Oracle's Metalink. I gather that this sort of inline declaration is not allowed in PL/SQL. By the way, your code also doesn't work in Oracle 8.1.6.

Here is the reference:

SQL operations on nested table

I found the following script that demonstrates
SQL operations on collections in Oracle 8.1

CREATE TYPE cutbacks_for_taxcuts AS
TABLE OF VARCHAR2(100);
/
CREATE TABLE lobbying_results (
activity VARCHAR2(200));

INSERT INTO lobbying_results
VALUES ('No tax on stock transactions');
INSERT INTO lobbying_results
VALUES ('Cut city income taxes');

DECLARE
nyc_devolution cutbacks_for_taxcuts :=
cutbacks_for_taxcuts (
'Stop rat extermination programs',
'Fire building inspectors',
'Close public hospitals');
BEGIN
DBMS_OUTPUT.PUT_LINE (
'How to Make the NYC Rich Much, Much Richer:');
FOR rec IN (
SELECT COLUMN_VALUE ohmy
FROM TABLE (
CAST (
nyc_devolution AS
cutbacks_for_taxcuts))
UNION
SELECT activity FROM lobbying_results)
LOOP
DBMS_OUTPUT.PUT_LINE (rec.ohmy);
END LOOP;
END;
/

It gives the following results:

How to Make the NYC Rich Much, Much Richer:
Close public hospitals
Cut city income taxes
Fire building inspectors
No tax on stock transactions
Stop rat extermination programs


I then tried the following srcipt:

DECLARE

TYPE cutbacks_for_taxcuts AS TABLE OF VARCHAR2(100);

nyc_devolution cutbacks_for_taxcuts :=
cutbacks_for_taxcuts (
'Stop rat extermination programs',
'Fire building inspectors',
'Close public hospitals');
BEGIN


DBMS_OUTPUT.PUT_LINE (
'How to Make the NYC Rich Much, Much Richer:');
FOR rec IN (
SELECT COLUMN_VALUE ohmy
FROM TABLE (
CAST (
nyc_devolution AS
cutbacks_for_taxcuts))
UNION
SELECT activity FROM lobbying_results)
LOOP
DBMS_OUTPUT.PUT_LINE (rec.ohmy);
END LOOP;
END;

This script gives me this result:
ERROR at line 1:
ORA-00600: internal error code, arguments: [15419], [severe error during PL/SQL execution], [], [],
[], [], [], []
ORA-06544: PL/SQL: internal error, arguments: [pfrrun.cfrbnd1()], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [0]


What is the differece between

CREATE TYPE cutbacks_for_taxcuts AS TABLE OF VARCHAR2(100);

and

TYPE cutbacks_for_taxcuts IS TABLE OF VARCHAR2(100);






--------------------------------------------------------------------------------

From: Oracle, krishna kumar sivasubramanian 19-Apr-00 07:45
Subject: Re : SQL operations on nested table


Hello

You could use the new 8i feature of selecting from a pl/sql nested table variable trhough sql.That is valid but the nested table must be declared at the sql level not locally to the pl/sql.That is why you are getting errors while compiling the second procedure.


Regards
S.Krishna Kumar