| Dynamic SQL and Ref Cursor [message #370051] |
Wed, 06 December 2000 02:09  |
HeyEun Yang
Messages: 1
Registered: December 2000
|
Junior Member |
|
|
Hi!
I have some trouble Dynamic SQL and Ref Cursor.(Oracle 8.1.5.0.0)
I made two examples.
Exam 1 is success but Exam 2 is fail.
I could not compile Exam 2.
I would like to use Dynamic SQL and return Ref Cursor results.
Please help me.
CREATE OR REPLACE PACKAGE pa_a IS
TYPE a_result_set IS REF CURSOR RETURN cas_smart_card%ROWTYPE;
END;
/
<Example 1 - success>
CREATE OR REPLACE PROCEDURE sp_a1( out_list1 IN OUT pa_a.a_result_set)
AS
BEGIN
OPEN out_list1 FOR SELECT * FROM cas_smart_card;
END;
/
<Example 2 - fail>
CREATE OR REPLACE PROCEDURE sp_a2(
out_list1 IN OUT pa_a.a_result_set)
AS
str varchar2(200);
BEGIN
str := 'SELECT * FROM a_table';
OPEN out_list1 FOR str;
END;
/
SQL> show error
Errors for PROCEDURE SP_A2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
8/2 PL/SQL: Statement ignored
8/7 PLS-00455: cursor 'OUT_LIST1' cannot be used in dynamic SQL OPEN
statement
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
