....
I get the table name as a parameter and I can't declare a record variable using %rowtype and because of this
I have to generate a column list and an Insert Select query. At the same time I want to return the rowid after insert.
How can I do this considering the dynamic table name
....
Still don't understand the need for ROWID here, as pointed by Connor on the above link, RETURNING clause is only possible on insert ...values(...) statement and not with INSERT ...SUB_QUERY.
For Dynamic insertion, you could rely on DBMS_SQL API.
demo@ORA11G> create or replace procedure load_data(
2 p_source varchar2,
3 p_destination varchar2 )
4 as
5 l_sql long;
6 l_cursor int;
7 l_source varchar2(30);
8 l_destination varchar2(30);
9 l_col_cnt int;
10 l_desc_t dbms_sql.desc_tab;
11 l_source_collist long;
12 l_dest_collist long;
13 l_rows int;
14 begin
15 l_source := dbms_assert.sql_object_name(p_source);
16 l_destination := dbms_assert.sql_object_name(p_destination);
17
18 l_cursor := dbms_sql.open_cursor;
19 dbms_sql.parse(l_cursor,'select * from '||l_source,dbms_sql.native);
20 dbms_sql.describe_columns(l_cursor,l_col_cnt,l_desc_t);
21 for i in 1..l_col_cnt
22 loop
23 l_source_collist := l_source_collist ||l_desc_t(i).col_name||',';
24 end loop;
25 l_source_collist := trim(',' from l_source_collist);
26 dbms_sql.close_cursor(l_cursor);
27
28 l_cursor := dbms_sql.open_cursor;
29 dbms_sql.parse(l_cursor,'select * from '||l_destination,dbms_sql.native);
30 dbms_sql.describe_columns(l_cursor,l_col_cnt,l_desc_t);
31 for i in 1..l_col_cnt
32 loop
33 l_dest_collist := l_dest_collist ||l_desc_t(i).col_name||',';
34 end loop;
35 l_dest_collist := trim(',' from l_dest_collist);
36 dbms_sql.close_cursor(l_cursor);
37
38 l_sql := ' insert into '||l_destination||' ( '||l_dest_collist ||' ) '||
39 ' select '||l_source_collist||' from '||l_source ;
40
41 l_cursor := dbms_sql.open_cursor;
42 dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);
43 l_rows := dbms_sql.execute(l_cursor);
44 dbms_sql.close_cursor(l_cursor);
45 dbms_output.put_line('Row count ='||l_rows);
46 end;
47 /
Procedure created.
demo@ORA11G> create table emp as select * from scott.emp;
Table created.
demo@ORA11G> create table emp_temp as select *
2 from scott.emp
3 where 1 = 0;
Table created.
demo@ORA11G> begin
2 load_data(
3 p_source=>'EMP',
4 p_destination=>'EMP_TEMP');
5 end;
6 /
Row count =14
PL/SQL procedure successfully completed.
demo@ORA11G> select count(*) from emp_temp;
COUNT(*)
----------
14
demo@ORA11G>