How to add table name to EXECUTE IMMEDIATE query?

Question

I have one question about "EXECUTE IMMEDIATE". I dynamically changed the table name in the following plsql expression

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 the following Execute Immediate query before the cursor?

 'select name, sname from :1 b,myobjects a where a.obj_id = b.obj_id' USING table_name; 

Or maybe there is a way to do this?

Thanks in advance.

Answer 1

You can use ref_cursor see http://docs.oracle.com/cd/B10500_01/appdev.920/a96590/adg09dyn.htm

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 cannot insert table names as parameters

Answer 2

For object names, you should use concatenation, not bind variables.

From the Dynamic chapter of SQL PL / SQL reference :

The database uses the values ​​of binding variables exclusively and does not interpret their contents in any way.

Associate variables with safety and efficiency. But they will not work with objects such as tables. If you pass the table name, then Oracle should interpret the contents, which will adversely affect security and performance.