Re: REF CURSOR returning a PL/SQL Table.

From: Adrian Billington <billiauk_at_yahoo.co.uk>
Date: 8 Feb 2002 00:49:46 -0800
Message-ID: <dee17a9f.0202080049.382faa93@posting.google.com>

Galen

Right then. I've had a look at your original post again and it must have something to do with the ref cursor declaration, but this is in the package that you've omitted from your post. Can you let me see the ref cursor declaration?

Consider my two simple examples of using ref cursors below. The first is using "static" SQL, and the second is using Native Dynamic SQL. There is no difference in the outcome:-

billia_at_ora816 SQL> set echo on
billia_at_ora816 SQL> --Create a package header simply to declare a user-defined type i.e. ref cursor type... billia_at_ora816 SQL> CREATE OR REPLACE PACKAGE pkg_udtypes   2 AS
  3 TYPE typ_ref_cursor IS REF CURSOR;   4 END;
  5 /

Package created.

billia_at_ora816 SQL>
billia_at_ora816 SQL> --Create a function that will supply the resultset from fixed ref cursor...
billia_at_ora816 SQL> CREATE OR REPLACE FUNCTION get_records_static RETURN pkg_udtypes.typ_ref_cursor
  2 AS
  3 cur_get_records pkg_udtypes.typ_ref_cursor;   4 BEGIN

  5  	 OPEN cur_get_records FOR
  6  	    SELECT owner
  7  	    ,	   object_name
  8  	    FROM   all_objects
  9  	    WHERE  ROWNUM < 11;
 10  	 RETURN cur_get_records;

 11 END;
 12 /

Function created.

billia_at_ora816 SQL>
billia_at_ora816 SQL> --Create a function that will supply the resultset from dynamic ref cursor...
billia_at_ora816 SQL> CREATE OR REPLACE FUNCTION get_records_nds (

  2  				sql_in IN VARCHAR2
  3  				)RETURN pkg_udtypes.typ_ref_cursor AS
  4  	 cur_get_records  pkg_udtypes.typ_ref_cursor;
  5  BEGIN
  6  	 OPEN cur_get_records FOR sql_in;
  7  	 RETURN cur_get_records;

  8 END;
  9 /

Function created.

billia_at_ora816 SQL>
billia_at_ora816 SQL> prompt Execute both functions to get the resultset...
Execute both functions to get the resultset... billia_at_ora816 SQL> col object_name format a30 billia_at_ora816 SQL> prompt Static SQL
Static SQL
billia_at_ora816 SQL> variable resultset refcursor billia_at_ora816 SQL> exec :resultset := get_records_static();

PL/SQL procedure successfully completed.

billia_at_ora816 SQL> print resultset

OWNER                          OBJECT_NAME


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

SYS                            /1001a851_ConstantDefImpl
SYS                            /10076b23_OraCustomDatumClosur
SYS                            /10322588_HandlerRegistryHelpe
SYS                            /1033c8a_SqlTypeWithMethods
SYS                            /103a2e73_DefaultEditorKitEndP
SYS                            /104b85c5_LogFileOutputStream
SYS                            /10501902_BasicFileChooserUINe
SYS                            /105072e7_HttpSessionBindingEv
SYS                            /106faabc_BasicTreeUIKeyHandle
SYS                            /1079c94d_NumberConstantData

10 rows selected.

billia_at_ora816 SQL> prompt Native Dynamic SQL Native Dynamic SQL
billia_at_ora816 SQL> exec :resultset := get_records_nds('SELECT owner,object_name FROM all_objects WHERE ROWNUM < 11');

PL/SQL procedure successfully completed.

billia_at_ora816 SQL> print resultset

OWNER                          OBJECT_NAME


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

SYS                            /1001a851_ConstantDefImpl
SYS                            /10076b23_OraCustomDatumClosur
SYS                            /10322588_HandlerRegistryHelpe
SYS                            /1033c8a_SqlTypeWithMethods
SYS                            /103a2e73_DefaultEditorKitEndP
SYS                            /104b85c5_LogFileOutputStream
SYS                            /10501902_BasicFileChooserUINe
SYS                            /105072e7_HttpSessionBindingEv
SYS                            /106faabc_BasicTreeUIKeyHandle
SYS                            /1079c94d_NumberConstantData

10 rows selected.

Adrian Received on Fri Feb 08 2002 - 02:49:46 CST