Hi,
Hope you are doing good and the Open World was exciting.
I have got 2 tables, Table1 has the columns and data that has to be inserted as rows into Table2.
I have shared an example of 5 columns, however the number is not constant. This can change with different tables.
I will be glad, if you could help on this with the best approach to have generic plsql without hardcoding the column names.
Below is the example,
Table1
------------
CREATE TABLE data_table
(
table_name VARCHAR2(100),
column_name VARCHAR2(100),
column_value VARCHAR2(500)
);
insert into data_table values ( 'TEST_TAB','ORDER_NUMBER','ORD1001');
insert into data_table values ( 'TEST_TAB','CUST_NAME','CUST1001');
insert into data_table values ( 'TEST_TAB','INVOICE_NUMBER','INV001');
insert into data_table values ( 'TEST_TAB','ORDER_DATE','04/11/2018 15:14:00');
insert into data_table values ( 'TEST_TAB','AMOUNT','1001');
commit;
select * from data_table
TABLE_NAME COLUMN_NAME COLUMN_VALUE
TEST_TAB ORDER_NUMBER ORD1001
TEST_TAB CUST_NAME CUST1001
TEST_TAB INVOICE_NUMBER INV001
TEST_TAB AMOUNT 1001
TEST_TAB ORDER_DATE 4/11/2018 15:14
Table2
------------
CREATE TABLE TEST_TAB
(
ORDER_NUMBER VARCHAR2(100),
CUST_NAME VARCHAR2(100),
INVOICE_NUMBER VARCHAR2(100),
ORDER_DATE DATE,
AMOUNT NUMBER
);
The 'column_name' from 'data_table' will have the same names of the columns in 'test_tab'.
(count of columns may change dynamically)
How can we read from 'data_table' and insert data into 'test_tab' which looks like this.
AMOUNT CUST_NAME INVOICE_NUMBER ORDER_DATE ORDER_NUMBER
1001 CUST1001 INV001 4/11/2018 15:14 ORD1001
Any help here is very much appreciated.
Thank you.
A little bit of DBMS_SQL will help here. I've added a "row_seq" column so that we can demo multiple rows
I'm using listagg to build up column name lists and bind variable lists.
SQL> CREATE TABLE data_table
2 (
3 row_seq int,
4 table_name VARCHAR2(30),
5 column_name VARCHAR2(30),
6 column_value VARCHAR2(30)
7 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into data_table values ( 1,'TEST_TAB','ORDER_NUMBER','ORD1001');
1 row created.
SQL> insert into data_table values ( 1,'TEST_TAB','CUST_NAME','CUST1001');
1 row created.
SQL> insert into data_table values ( 1,'TEST_TAB','INVOICE_NUMBER','INV001');
1 row created.
SQL> insert into data_table values ( 1,'TEST_TAB','ORDER_DATE','04/11/2018 15:14:00');
1 row created.
SQL> insert into data_table values ( 1,'TEST_TAB','AMOUNT','1001');
1 row created.
SQL>
SQL> insert into data_table values ( 2,'TEST_TAB','ORDER_NUMBER','ORD1002');
1 row created.
SQL> insert into data_table values ( 2,'TEST_TAB','CUST_NAME','CUST1002');
1 row created.
SQL> insert into data_table values ( 2,'TEST_TAB','INVOICE_NUMBER','INV002');
1 row created.
SQL> insert into data_table values ( 2,'TEST_TAB','ORDER_DATE','02/11/2018 15:14:00');
1 row created.
SQL> insert into data_table values ( 2,'TEST_TAB','AMOUNT','1002');
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> create table test_tab (
2 order_number varchar2(10),
3 cust_name varchar2(10),
4 invoice_number varchar2(10),
5 order_date varchar2(30),
6 amount varchar2(10)
7 );
Table created.
SQL>
SQL> select * from data_table;
ROW_SEQ TABLE_NAME COLUMN_NAME COLUMN_VALUE
---------- ------------------------------ ------------------------------ ------------------------------
1 TEST_TAB ORDER_NUMBER ORD1001
1 TEST_TAB CUST_NAME CUST1001
1 TEST_TAB INVOICE_NUMBER INV001
1 TEST_TAB ORDER_DATE 04/11/2018 15:14:00
1 TEST_TAB AMOUNT 1001
2 TEST_TAB ORDER_NUMBER ORD1002
2 TEST_TAB CUST_NAME CUST1002
2 TEST_TAB INVOICE_NUMBER INV002
2 TEST_TAB ORDER_DATE 02/11/2018 15:14:00
2 TEST_TAB AMOUNT 1002
10 rows selected.
SQL>
SQL> declare
2 l_sql varchar2(32000);
3 l_cur pls_integer := dbms_sql.open_cursor;
4 l_execute pls_integer;
5 begin
6 for i in (
7 select table_name,
8 column_name,
9 row_number() over ( partition by table_name, row_seq order by column_name ) as seq,
10 count(*) over ( partition by table_name, row_seq ) as col_cnt,
11 listagg(column_name,',') within group ( order by column_name ) over ( partition by table_name, row_seq ) as cols,
12 listagg(':'||column_name,',') within group ( order by column_name ) over ( partition by table_name, row_seq ) as bindcols,
13 column_value
14 from data_table
15 order by table_name, row_seq, column_name
16 ) loop
17 if i.seq = 1 then
18 l_sql := 'insert into '||i.table_name||'('||i.cols||') values ('||i.bindcols||')';
19 dbms_sql.parse(l_cur,l_sql,dbms_sql.native);
20 end if;
21 dbms_sql.bind_variable(l_cur,i.column_name,i.column_value);
22 if i.seq = i.col_cnt then
23 l_execute := dbms_sql.execute(l_cur);
24 end if;
25 end loop;
26 dbms_sql.close_cursor(l_cur);
27 end;
28 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from test_tab;
ORDER_NUMB CUST_NAME INVOICE_NU ORDER_DATE AMOUNT
---------- ---------- ---------- ------------------------------ ----------
ORD1001 CUST1001 INV001 04/11/2018 15:14:00 1001
ORD1002 CUST1002 INV002 02/11/2018 15:14:00 1002
2 rows selected.
SQL>