ORACLE reference cursor question
Date: 1996/05/22
Message-ID: <4nvp95$gu1_at_eccdb1.pms.ford.com>#1/1
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?
2. Why do I get an error when I use dbms_sql.parse to parse the cursor?
3. Does anybody have examples in using both dbms_sql calls and ref cursors in
one procedure?
You could mail me at pmagesh_at_pt4064.pto.ford.com or reply to this newsgroup. Thanks. Received on Wed May 22 1996 - 00:00:00 CEST