Re: PL/SQL Arrays to Cursors and back to arrays

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 9 Jun 2002 08:05:35 -0700
Message-ID: <advqrv02nvi@drn.newsguy.com>

In article <e9e91bd5.0206081356.67adafcb_at_posting.google.com>, Aaron.Cowie_at_ntlworld.com says...
>
>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
>

well, l_data_list is a collection of objects -- not of "scalars"

this will work:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> DECLARE

  2     TYPE io_cursor          IS REF CURSOR;
  3     l_data_list           data_list := data_list();
  4     l_data_list_c         io_cursor;
  5  BEGIN
  6     -- put one row into the collection (array)
  7     --
  8     l_data_list.EXTEND;
  9     l_data_list(1) := data_rec('x','y',1,0,0,'z');
 10  
 11     -- put the array into a Cursor variable
 12     --
 13     OPEN l_data_list_c FOR
14        'SELECT
data_rec(data_name,data_style,data_name_hash,level_no,row_no,value_char)
 15           FROM TABLE(CAST(:l_data_list AS data_list))' using l_data_list;
 16  
17     -- get the first (and only) row out of the cursor and put it into the
first element of the collection
 18     --
 19     FETCH l_data_list_c INTO l_data_list(1);
 20  
 21     -- close the open cursor
 22     --
 23     CLOSE l_data_list_c;

 24 END;
 25 /

PL/SQL procedure successfully completed.

(used dynamic sql, plsql didn't like the construct in 8i). Or

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> DECLARE

  2     TYPE io_cursor          IS REF CURSOR;
  3     l_data_list           data_list := data_list();
  4     l_data_list_c         io_cursor;
  5  BEGIN
  6     -- put one row into the collection (array)
  7     --
  8     l_data_list.EXTEND;
  9     l_data_list(1) := data_rec('x','y',1,0,0,'z');
 10  
 11     -- put the array into a Cursor variable
 12     --
 13     OPEN l_data_list_c FOR
 14        SELECT *
 15           FROM TABLE(CAST(l_data_list AS data_list));
 16  
17     -- get the first (and only) row out of the cursor and put it into the
first element of the collection
 18     --
19     FETCH l_data_list_c INTO l_data_list(1).data_name,
l_data_list(1).data_style,
20           l_data_list(1).data_name_hash, l_data_list(1).level_no,
l_data_list(1).row_no,
 21           l_data_list(1).value_char;
 22  
 23     -- close the open cursor
 24     --
 25     CLOSE l_data_list_c;

 26 END;
 27 /

PL/SQL procedure successfully completed.

will work as well.

>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.

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sun Jun 09 2002 - 10:05:35 CDT