Re: what's wrong with this pl/sql block?

From: padderz <member_at_dbforums.com>
Date: 5 Mar 2002 09:29:44 -0600
Message-ID: <3c84e468_1_at_spamkiller.newsgroups.com>


What version are you on? I cannot recreate internal error. In any case you need objects option installed. Try...

SELECT value FROM v$option WHERE parameter = 'Objects';

There is nothing wrong with OPEN FOR syntax. To select from collection, collection type must be created as stored type in the database, not just declared in PL/SQL, e.g.

CREATE TYPE newstruct AS OBJECT (c1 NUMBER, c2 NUMBER); /

CREATE TYPE ntab AS TABLE OF newstruct; /

Then you can attempt what you are trying...

DECLARE TYPE cursortype IS REF CURSOR;

  nlist ntab := ntab (); mycursor cursortype; BEGIN nlist.extend (2);   nlist (1) := newstruct (1, 2); nlist (2) := newstruct (11, 22);

  OPEN mycursor FOR SELECT t.* FROM TABLE (CAST (nlist AS ntab)) t;   CLOSE mycursor; END;

--
Padderz
SYSOP, RevealNet PL/SQL Pipeline
Senior Oracle Developer, Cellular Operations UK

Posted via dBforums
http://dbforums.com
Received on Tue Mar 05 2002 - 16:29:44 CET

Original text of this message