Help with Ref Cursor [message #38157] |
Tue, 26 March 2002 06:14 |
Rick Cale
Messages: 111 Registered: February 2002
|
Senior Member |
|
|
I am trying to test the following package with a test procedure and having no luck at all. I have a package procedure that returns a ref cursor. I am having trouble testing this in the procedure tst_pkg_args. My goal is to dynamically build a call to a procedure using the info contained in the returned ref cursor.
Any help is appreciated.
Thanks
Rick
CREATE OR REPLACE PACKAGE pkg_args AS
TYPE ref_args IS REF CURSOR;
PROCEDURE get_args (i_proc_name IN VARCHAR2,i_pkg_name IN VARCHAR2,refargs IN OUT ref_args);
END pkg_args;
/
CREATE OR REPLACE PACKAGE BODY pkg_args AS
PROCEDURE get_args (i_proc_name IN VARCHAR2,i_pkg_name IN VARCHAR2,refargs IN OUT ref_args) AS
BEGIN
OPEN refargs FOR 'select argument_name || '','' || data_length || '','' ||
data_precision || '','' || data_scale || '', '' || data_type || '', '' || in_out
from all_arguments where object_name = ' ||''''|| i_proc_name || ''''||
'and package_name = '||''''||i_pkg_name||'''';
END;
END pkg_args;
/
CREATE OR REPLACE PROCEDURE tst_pkg_args AS
TYPE cur1 IS ref cursor;
cur cur1;
var1 varchar2(100);
var2 number;
var3 number;
var4 varchar2(33);
var5 varchar2(10);
begin
pkg_args.GET_ARGS('SP_FETCHCLINICIANDETAIL','',cur);
OPEN cur;
LOOP
FETCH cur INTO var1,var2,var3,var4,var5;
END LOOP;
end;
/
|
|
|
Re: Help with Ref Cursor [message #38162 is a reply to message #38157] |
Tue, 26 March 2002 08:51 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Rick, you won't be able to fetch columns out of a weak ref cursor like that. The ref cursor will need to be based on something like user_arguments%rowtype.
CREATE OR REPLACE PACKAGE pkg_args AS
TYPE ref_args IS ref cursor return user_arguments%rowtype;
PROCEDURE get_args (i_proc_name IN VARCHAR2,i_pkg_name IN VARCHAR2,refargs IN OUT ref_args);
END pkg_args;
/
CREATE OR REPLACE PACKAGE BODY pkg_args AS
PROCEDURE get_args (i_proc_name IN VARCHAR2,i_pkg_name IN VARCHAR2,refargs IN OUT ref_args) AS
BEGIN
OPEN refargs FOR
select *
from user_arguments
where object_name = i_proc_name
and package_name = i_pkg_name;
END;
END pkg_args;
/
declare
cur pkg_args.ref_args;
val user_arguments%rowtype;
begin
pkg_args.GET_ARGS('GET_ARGS', 'PKG_ARGS', cur);
loop
fetch cur into val;
exit when cur%notfound;
dbms_output.put_line( val.argument_name);
dbms_output.put_line( val.data_type);
end loop;
close cur;
end;
|
|
|