sql select statement as bind variable for dynamic plsql block

Question

I am trying to run plsql anonymous block using execute immediate and the plsql block contains a bind variable for which the value is a sql select statement. But it seems this does not work. Is there any solution for to solve this. E.g.

BEGIN
  V_SQL:='SELECT emp_id FROM  emp  WHERE  dept_id=10;
  PLSQL_BLOCK:='DECLARE
    type emp_type
    IS
    TABLE OF NUMBER;
    emp_id emp_type;
    BEGIN
        EXECUTE IMMEDIATE :1 BULK COLLECT INTO emp_id;  
    END';
EXECUTE IMMEDIATE PLSQL_BLOCK USING V_SQL;

Answer 1

If I understand well, you need to run an entire dynamic PLSQL block by using as SQL query as a bind variable; if so, you can try this way:

SQL> declare
  2      vPlSqlBlock varchar2(10000);
  3      vSQL        varchar2(1000);
  4  BEGIN
  5    vSQL:='SELECT 1 from dual';
  6    --
  7    vPlSqlBlock:='DECLARE
  8      type emp_type IS TABLE OF NUMBER;
  9      emp_id   emp_type;
 10      vSQLDyn  varchar2(1000) := :1;
 11      BEGIN
 12          EXECUTE IMMEDIATE vSQLDyn BULK COLLECT INTO emp_id;
 13          --
 14          /* whatever you need to do in your block */
 15          for i in emp_id.first .. emp_id.last loop
 16              dbms_output.put_line(emp_id(i));
 17          end loop;
 18      END;';
 19
 20      EXECUTE IMMEDIATE vPlSqlBlock USING vSQL;
 21  end;
 22  /
1

PL/SQL procedure successfully completed.

SQL> declare
  2      vPlSqlBlock varchar2(10000);
  3      vSQL        varchar2(1000);
  4  BEGIN
  5    vSQL:='SELECT 1 from dual';
  6    --
  7    vPlSqlBlock:='DECLARE
  8      type emp_type IS TABLE OF NUMBER;
  9      emp_id   emp_type;
 10      vSQLDyn  varchar2(1000) := :1;
 11      BEGIN
 12          EXECUTE IMMEDIATE vSQLDyn BULK COLLECT INTO emp_id;
 13          /* this does nothing */
 14      END;';
 15
 16      EXECUTE IMMEDIATE vPlSqlBlock USING vSQL;
 17  end;
 18  /

PL/SQL procedure successfully completed.

SQL>