How to add a table name in EXECUTE IMMEDIATE query?

Question

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.

Answer 1

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.

Answer 2

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