Re: ORACLE reference cursor question

In article <4nvp95$gu1_at_eccdb1.pms.ford.com>, pmagesh_at_tc1225.ford.com (Priya Magesh) writes:
|> Hi,
|> I have a question on using reference cursors in PL/SQL .
|> The following PL/SQL procedure uses dbms_sql calls and ref cursors.
|>
|> /* The header for the procedure */
|> create or replace package test is
|> type curtype is ref cursor return record_values;
|> type record_values is record (username varchar2(8),
|> userid varchar2(8));
|> procedure test(user_name IN varchar2, cur IN OUT curtype);
|> end test_pkg;
|>
|> /* The body of the procedure */
|> create or replace package body test_pkg is
|> procedure test (username IN varchar2,
|> cur IN OUT curtype ) is
|> begin
|> str := 'select username, userid from app_roles where uname = '||':userna
|> me';
|> /* Should I be opening a cursor when I am using reference cursors? */
|> cur := dbms_sql.open_cursor;
|> /*
|> Gives an error message as follows:
|> PLS-00306: wrong number or types of arguments in call to 'PARSE'
|> PL/SQL: Statement ignored
|> */
|> dbms_sql.parse(cur, str, dbms_sql.v7);
|> . (bind calls and other stuff)
|> .
|> .
|> .
|> .
|> end test;
|> end test_pkg;
|>
|> My questions are:
|> 1. Should I use dbms_sql.open_cursor to open the cursor when I am using referenc
|> e cursors?

No. Use the OPEN <cursor> FOR SELECT ... syntax.

|> 2. Why do I get an error when I use dbms_sql.parse to parse the cursor?

DBMS_SQL and cursor variables are separate things. They don't work together - the cursor numbers used in DBMS_SQL do not map to cursor variables (although it would be a good enhancement request if they did).

This means that you can't use cursor variables with dynamic SQL, unless you use OCI to dynamicly build the PL/SQL blocks.

|> 3. Does anybody have examples in using both dbms_sql calls and ref cursors in
|> one procedure?

That I don't have. But since they don't interact together, you should be able to combine two separate examples.

|>
|> You could mail me at pmagesh_at_pt4064.pto.ford.com or reply to this newsgroup.
|> Thanks.

You're welcome.

-- 
------------------------------------------------------------------------
Scott Urman            Oracle Corporation           surman_at_us.oracle.com
------------------------------------------------------------------------
Author of _Oracle PL/SQL Programming_ ISBN 0-07-882176-2
Published by Oracle Press - http://www.osborne.com/oracle/index.htm
------------------------------------------------------------------------
"The opinions expressed here are my own, and are not necessarily that of
 Oracle Corporation"
------------------------------------------------------------------------