execute immediate with dynamic table name passed to a procedure

Question

I have a view view_test_dynamic which contains insert statements as a single column as given below.

I need to populate this single column value into a table. example as test_dynamic table as below.

I have multiple views like this and I need to populate into different tables.

When I create procedure in a normal way by declaring cursor for one view it works.

I would like to use dynamic pl/sql by passing the view name to the procedure as below.

I am getting error at execute immediate. the error says " expression is of wrong type"

Can some one please help?

create or replace view view_test_dynamic as

select 'insert into test_dynamic(cust_id,address) values ('||cust_id||','''||address||''')' as trans_out
 
from

    (
    select 10 cust_id,'9 Help Street, Level 4' address from dual union all
    select 11 cust_id,'22 Victoria Street' address from dual union all
    select 12 cust_id,'1495 Franklin Str.' address from dual union all
    select 13 cust_id,'30 Hasivim St.,Petah-Tikva' address from dual union all
    select 14 cust_id,'2 Jakaranda St' address from dual union all
    select 15 cust_id,'61, Science Park Rd' address from dual union all
    select 16 cust_id,'61, Social park road.' address from dual union all
    select 17 cust_id,'Av. Hermanos Escobar 5756' address from dual union all
    select 18 cust_id,'Ave. Hermanos Escobar 5756' address from dual union all
    select 19 cust_id,'8000 W FLORISSANT Ave.' address from dual union all
    select 20 cust_id,'8600 MEMORIAL PKWY SW' address from dual union all
    select 21 cust_id,'8200 FLORISSANTMEMORIALWAYABOVE SW' address from dual union all
    select 22 cust_id,'8600 MEMORIALFLORISSANT PKWY SW.' address from dual
    ) t1;

Table:

create table test_dynamic

(

cust_id number,

address varchar2(100)

);

Procedure:

CREATE OR REPLACE PROCEDURE POP_TABLES_DYNAMIC(p_table_name in varchar2) as

l_cursor sys_refcursor;

l_trans_out dbms_sql.varchar2_table;

l_processed_cnt number := 0;

l_rec varchar2(500);


BEGIN


  dbms_output.put_line( 'Started' );

  open l_cursor for 'select trans_out from ' || p_table_name;

  loop
    fetch l_cursor BULK COLLECT into l_trans_out LIMIT 5000;

    for i in 1 .. l_trans_out.count
    loop

      execute immediate l_trans_out using l_trans_out(i);

      commit;

      l_processed_cnt := l_processed_cnt+1;

    end loop;

    exit when l_cursor%notfound;

  end loop;

  close l_cursor;

  dbms_output.put_line( 'processed ' || l_processed_cnt || ' records' );


  dbms_output.put_line('Ended');

END;

/

sho err;

Answer 1

For sample data you posted:

SQL> select * from view_test_dynamic;

TRANS_OUT
-------------------------------------------------------------------------------------------
insert into test_dynamic(cust_id,address) values (10,'9 Help Street, Level 4')
insert into test_dynamic(cust_id,address) values (11,'22 Victoria Street')
insert into test_dynamic(cust_id,address) values (12,'1495 Franklin Str.')
insert into test_dynamic(cust_id,address) values (13,'30 Hasivim St.,Petah-Tikva')
insert into test_dynamic(cust_id,address) values (14,'2 Jakaranda St')
insert into test_dynamic(cust_id,address) values (15,'61, Science Park Rd')
insert into test_dynamic(cust_id,address) values (16,'61, Social park road.')
insert into test_dynamic(cust_id,address) values (17,'Av. Hermanos Escobar 5756')
insert into test_dynamic(cust_id,address) values (18,'Ave. Hermanos Escobar 5756')
insert into test_dynamic(cust_id,address) values (19,'8000 W FLORISSANT Ave.')
insert into test_dynamic(cust_id,address) values (20,'8600 MEMORIAL PKWY SW')
insert into test_dynamic(cust_id,address) values (21,'8200 FLORISSANTMEMORIALWAYABOVE SW')
insert into test_dynamic(cust_id,address) values (22,'8600 MEMORIALFLORISSANT PKWY SW.')

13 rows selected.

SQL> select * from test_dynamic;

no rows selected

consider such a (simpler) code:

SQL> create or replace procedure pop_tables_dynamic (p_table_name in varchar2)
  2  is
  3    rc    sys_refcursor;
  4    l_str view_test_dynamic.trans_out%type;
  5  begin
  6    open rc for 'select trans_out from ' || dbms_assert.sql_object_name(p_table_name);
  7    loop
  8      fetch rc into l_str;
  9      exit when rc%notfound;
 10      execute immediate l_str;
 11    end loop;
 12    close rc;
 13  end;
 14  /

Procedure created.

SQL> exec pop_tables_dynamic('view_test_dynamic');

PL/SQL procedure successfully completed.

Result:

SQL> select * from test_dynamic;

   CUST_ID ADDRESS
---------- ------------------------------------------------------------
        10 9 Help Street, Level 4
        11 22 Victoria Street
        12 1495 Franklin Str.
        13 30 Hasivim St.,Petah-Tikva
        14 2 Jakaranda St
        15 61, Science Park Rd
        16 61, Social park road.
        17 Av. Hermanos Escobar 5756
        18 Ave. Hermanos Escobar 5756
        19 8000 W FLORISSANT Ave.
        20 8600 MEMORIAL PKWY SW
        21 8200 FLORISSANTMEMORIALWAYABOVE SW
        22 8600 MEMORIALFLORISSANT PKWY SW.

13 rows selected.