PL/SQL Arrays to Cursors and back to arrays

I am trying to use an Oracle package that internally uses an array of objects,
but wrap this up in an interface layer to convert this to and from a cursor variable so that Delphi can see it.

Here is some test code that errors when I run it, but I cannot see why.

Here are the definitions of the Object and Table of Objects

CREATE OR REPLACE TYPE data_rec AS OBJECT (data_name VARCHAR2(40)
,data_style
VARCHAR2(4)
,data_name_hash NUMBER(8)
,level_no NUMBER(8)
,row_no NUMBER(8)
,value_char
VARCHAR2(40)); /

CREATE or REPLACE TYPE DATA_LIST AS TABLE OF DATA_REC; /

Here is the test PL/SQL I am using that generates the error

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

with a line number that corresponds to the FETCH statement.

DECLARE    TYPE io_cursor IS REF CURSOR;

   l_data_list           data_list := data_list();
   l_data_list_c         io_cursor;

BEGIN    DBMS_OUTPUT.ENABLE (10000000);

• put one row into the collection (array) -- l_data_list.EXTEND; l_data_list(1) := data_rec('x','y',1,0,0,'z');
• put the array into a Cursor variable -- OPEN l_data_list_c FOR SELECT * FROM TABLE(CAST(l_data_list AS data_list));
• get the first (and only) row out of the cursor and put it into the first element of the collection -- FETCH l_data_list_c INTO l_data_list(1);
• close the open cursor -- CLOSE l_data_list_c;

END;
/

Can anyone tell me why I get the data type mismatch? Thanks for your time. Received on Sat Jun 08 2002 - 16:56:05 CDT