I have one question about "EXECUTE IMMEDIATE". I have dynamicly changed table name in next plsql statement
DECLARE
TYPE CurTyp IS REF CURSOR;
cur CurTyp;
str1 VARCHAR2(30);
str2 VARCHAR2(30);
table_name VARCHAR2(30);
BEGIN
select data
into table_name
from ref
where o_id = 111
and a_id = 222;
OPEN cur FOR
'select name, sname from :1 b,myobjects a where a.obj_id = b.obj_id'
USING table_name;
LOOP
FETCH cur INTO str1, str2;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line(str1||str2);
END LOOP;
CLOSE cur;
END
Is it possible to read the result of next Execute Immediate query to cursor?
'select name, sname from :1 b,myobjects a where a.obj_id = b.obj_id'
USING table_name;
Or maybe is there any way to do this?
Thanks in advance.
For object names you must use concatenation, not bind variables.
From the Dynamic SQL chapter of the PL/SQL Language Reference:
The database uses the values of bind variables exclusively and does not interpret their contents in any way.
Bind variables help with security and performance. But they won't work with objects like tables. If you pass in a table name then Oracle must interpret the contents, which will negate the security and performance benefits.
You can use ref_cursor see http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm
the example:
CREATE OR REPLACE PROCEDURE query_invoice(
month VARCHAR2,
year VARCHAR2) IS
TYPE cur_typ IS REF CURSOR;
c cur_typ;
query_str VARCHAR2(200);
inv_num NUMBER;
inv_cust VARCHAR2(20);
inv_amt NUMBER;
BEGIN
query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year
|| ' WHERE invnum = :id';
OPEN c FOR query_str USING inv_num;
LOOP
FETCH c INTO inv_num, inv_cust, inv_amt;
EXIT WHEN c%NOTFOUND;
-- process row here
END LOOP;
CLOSE c;
END;
/
but as @jonearles said, you can't insert the table names as params
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With