Can we use collection variable as a table in execute immediate?

Question

Declare

Type t_approved_node is record( node_rowid Hr 
node_rowid%type, Node_+type hr.node_type%type);

Type t_val is table of t_approved_node Index by pls_integer;

V_node t_val;

V_tab varchar2(20);
V_col varchar2(400);
V_nrf_flg hr.hr_flag%type;
V_ubrf_flg hr.hr_flag%type := 3;
V_col_str varchar2(4000);

Begin

Begin
Select hr_flag into v_nrf_flg from hr;
End;

Begin
Select h.node_rowid, h.node_type bulk collect into v_node 
 from hr h,  hr_attr_wfm  haw
 Where h.hr_relation_id = haw.uc_hr_relation_id
 And h.node_type = 'UBR';

Begin
V_tab := 'UC_UBR';
Select listagg(column_name, ',' within group(order by 
column_id)
Into v_col from user_tab_columns where table_name = v_tab;
End;

 V_col_str := regex_replace( v_col, 'HR_FLAG', v_ubrf_flg);
 Execute immediate ' insert into ' || v_tab || '( ' ||
                                  V_col || ') ' || ' select '|| v_col_str || ' from ' ||
                                  V_tab || 'R ' || q' [ where node_type = ' UBR' a 
and hr_flag =:1 and  exists( ] ' || ' select 1 ' || ' from table( ' || 
v_node ||  ')y' || q' [ where y.node_rowid = R.node_rowid ] )'
Using v_nrf_flag;

End;

End;

I was trying to execute above block getting below error.

Wrong number or types of arguments in call to ||

Final query should be like

insert into UC_UBR ( v_col)/*3 columns into v_col variable*/
select v_col_str /* 3 columns in v_col_str variable*/ from UC_UBR R where  hr_flag =:1
and    exists
       (select 1 from table(v_node) /*collection variable*/ y
        where y.node_rowid = r.node_rowod;

Can anyone help on this?

Answer 1

Your sample code is full of errors and does not make any sense at all. But if I focus on your question, then the answer is "yes". See this example:

CREATE OR REPLACE TYPE t_app AS OBJECT( nodeid NUMBER, Nodetype VARCHAR2(100));
CREATE OR REPLACE TYPE t_val IS TABLE OF t_app;

DECLARE
    
    V_node t_val;
    V_result t_val;
    V_app t_app;
    V_count NUMBER;
    Sql_stmt VARCHAR2(100);
    
    nodeid NUMBER; 
    Nodetype VARCHAR2(100);

BEGIN

    SELECT t_app(nodeid, Nodetype) BULK COLLECT INTO V_node FROM HR;
    
    Sql_stmt := 'SELECT count(*) FROM TABLE(:t)';
    EXECUTE IMMEDIATE Sql_stmt INTO V_count USING V_node;
    DBMS_OUTPUT.PUT_LINE ( 'V_count = ' || V_count );

    Sql_stmt := 'SELECT nodeid, Nodetype FROM TABLE(:t) WHERE ROWNUM = 1';
    EXECUTE IMMEDIATE Sql_stmt INTO nodeid, Nodetype USING V_node;
    DBMS_OUTPUT.PUT_LINE ( 'nodeid = ' || nodeid  );
    DBMS_OUTPUT.PUT_LINE ( 'Nodetype = ' || Nodetype  );

    Sql_stmt := 'SELECT t_app(nodeid, Nodetype) FROM TABLE(:t) WHERE ROWNUM = 1';
    EXECUTE IMMEDIATE Sql_stmt INTO V_app USING V_node;
    DBMS_OUTPUT.PUT_LINE ( 'V_app =  ' || XMLTYPE(V_app).getClobVal()  );

    Sql_stmt := 'SELECT t_app(nodeid, Nodetype) FROM TABLE(:t)';
    EXECUTE IMMEDIATE Sql_stmt BULK COLLECT INTO V_result USING V_node;

END;

Answer 2

It would help if you posted real code you used, because this is full of syntax errors (missing sql_stmt local variable declaration, put.line (?)).

I have no idea what you plan to do with such a select statement as you can't execute it, it doesn't make any sense but - here you go; see line #20.

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2     TYPE t_app IS RECORD
  3     (
  4        nodeid     NUMBER,
  5        Nodetype   VARCHAR2 (20)
  6     );
  7
  8     TYPE t_val IS TABLE OF t_app
  9        INDEX BY PLS_INTEGER;
 10
 11     V_node    t_val;
 12     V_tab     VARCHAR2 (20);
 13
 14     sql_stmt  VARCHAR2 (200);
 15  BEGIN
 16     SELECT empno, ename
 17       BULK COLLECT INTO v_node
 18       FROM emp;
 19
 20     Sql_stmt := 'select 1 from (' || v_node (1).nodeid || 'Y)';
 21
 22     DBMS_OUTPUT.put_line (sql_stmt);
 23  END;
 24  /
select 1 from (7369Y)

PL/SQL procedure successfully completed.

SQL>