Defining Collections with variables

Question

How can I define Collections in PL/SQL with variables?

Eg.

v_owner varchar(128) := 'SCHEMA_USER';
v_tablename varchar(128) := 'TABLENAME';

TYPE t_tab IS TABLE OF SCHEMA_USER.TABLENAME%ROWTYPE;
v_tab t_tab;

What I want is to use the variables instead of the names of the owner/table. Something like that:

TYPE t_tab IS TABLE OF v_owner.v_tablename%ROWTYPE;

But that does not work of course.

Any idea?

Answer 1

You need to use dynamic PL/SQL something like this:

SQL> DECLARE
  2     v_owner varchar(128) := 'MYSCHEMA';
  3     v_tablename varchar(128) := 'EMP';
  4     v_str LONG;
  5  BEGIN
  6     v_str := 'DECLARE TYPE t_tab IS TABLE OF ' || v_owner || '.' || v_tablename || '%ROWTYPE;'
  7               || ' v_tab t_tab;'
  8               || ' BEGIN'
  9               || '   SELECT * BULK COLLECT INTO v_tab'
 10               || '     FROM emp WHERE empno = :input1;'
 11               || '   dbms_output.put_line(v_tab(1).ename);'
 12               || 'END;';
 13     EXECUTE IMMEDIATE v_str USING 7839;
 14  END;
 15  /
KING

PL/SQL procedure successfully completed.

Answer 2

Thank you very much for the response.

I had to modify the whole thing a bit, but that worked:

DECLARE
v_owner varchar(128) := 'SCHEMA';
     v_tablename varchar(128) := 'TABLE';
     v_str LONG;
  BEGIN
v_str := 'DECLARE TYPE t_tab IS TABLE OF ' || v_owner || '.' || v_tablename || '%ROWTYPE;'
               || ' v_tab t_tab;'
               || ' BEGIN'
               || '   SELECT * BULK COLLECT INTO v_tab'
               || '     FROM ' || v_owner || '.' || v_tablename ||';'
               || 'END;';
     EXECUTE IMMEDIATE v_str;
  END;
/