This discussion is archived
6 Replies Latest reply: Feb 7, 2013 6:22 AM by 988960 RSS

Generate Insert statements with Dynamic Column name

988960 Newbie
Currently Being Moderated
The Oracle Database is 11.2.0.1.0
Below is the PL/SQL block to generate Insert statement of the table SMTB_Current_users and its working fine. Please search for the tag 'sachin' and I the column name should dynamic instead of writing the cols for different table whose insert statement going to generate. The variable r(k) is already a table type and to concatenate it with dynamic col_name is the main problem which I am facing. For testing purpose on your schema replace smtb_current_users with your table name.
------------------------------------------------------------------
Declare
tab_name VARCHAR2(50);
TYPE p IS TABLE OF smtb_current_users%ROWTYPE INDEX BY BINARY_INTEGER;
type ty_col_tab is table of cols%rowtype index by binary_integer;
col_tab ty_col_tab;
r p;
k number;
str VARCHAR2(32767);
val_str VARCHAR2(32767);
g VARCHAR2(32767);
c VARCHAR2(20);
Cursor Cur (tab_name VARCHAR2) IS select column_name from cols where table_name = tab_name order by column_id;
Begin
--global.pr_init('001','USER06');
SELECT * bulk collect INTO col_tab FROM cols;
k:=1;
tab_name:='SMTB_CURRENT_USERS';
for x in Cur(tab_name)
Loop
--begin
if k = 1 then
str := 'INSERT INTO ' || tab_name || '(' || x.column_name;
else
str := str || chr(10) || ','||x.column_name;
end if;
/*exception when others then
--dbms_output.put_line(r(k).user_id || SQLERRM);
dbms_output.put_line(k || '');
end; */
k:=k+1;
End loop;
str := str || ') ' || 'values (';
k:=1;
SELECT * bulk collect INTO r FROM smtb_current_users;
For i in 1..r.COUNT
Loop
--for y in Cur(tab_name)
--Loop
--c:=y.column_name;
val_str := str || chr(39) || r(k).user_id || chr(39); -- sachin
val_str := val_str || ',';
val_str := val_str || chr(39) || r(k).TERMINAL || chr(39);
val_str := val_str || ',';
val_str := val_str || chr(39) || r(k).START_TIME || chr(39);
val_str := val_str || ',';
val_str := val_str || chr(39) || r(k).HOME_BRANCH || chr(39);
val_str := val_str || ',';
val_str := val_str || chr(39) || r(k).CURRENT_BRANCH || chr(39);
val_str := val_str || ',';
val_str := val_str || chr(39) || r(k).CURRENT_MODULE || chr(39);
val_str := val_str || ',';
val_str := val_str || chr(39) || r(k).SEQUENCE_NO || chr(39);
val_str := val_str || ')';
val_str := val_str || ';';
val_str := val_str || chr(10);
val_str := val_str || '/';
g:= g || chr(10);
g:= g || val_str;
--dbms_output.put_line(c);
dbms_output.put_line('------------------');
--End Loop;
k:=k+1;
End Loop;
g := g || chr(10);
g := g || 'commit;' || chr(10) || '/';

dbms_output.put_line(g);
--debug.pr_debug('FX',g);
End;

Edited by: 985957 on Feb 5, 2013 4:37 AM
  • 1. Re: Generate Insert statements with Dynamic Column name
    Karthick_Arp Guru
    Currently Being Moderated
    I did this long time back. May be this helps. But it has lot of limitation. But you can expand it.
    SQL> create or replace procedure generate_table_insert
      2  (
      3     pOwnerName in  varchar2,
      4     pTableName in  varchar2,
      5     pRc        out sys_refcursor,
      6     pWhere     in  varchar2 default ''
      7  )
      8  is
      9     lColumnList varchar2(32000);
     10     lSelectList varchar2(32000);
     11     lFinalSql   varchar2(32000);
     12     lWhere      varchar2(32000);
     13  begin
     14     if pWhere is null then
     15             lWhere := '1=1';
     16     else
     17             lWhere := pWhere;
     18     end if;
     19     for i in (
     20                select lower(column_name) column_name,
     21                       data_type
     22                  from all_tab_columns
     23                 where owner      = pOwnerName
     24                   and table_name = pTableName
     25                 order
     26                    by column_id
     27              )
     28     loop
     29             lColumnList := lColumnList || ', ' || i.column_name;
     30             case when i.data_type in ('VARCHAR2', 'CHAR') then
     31                     lSelectList := lSelectList || q'[|| ', ' || ''''||]' || i.column_name || q'[||'''']';
     32                  when i.data_type = 'NUMBER' then
     33                     lSelectList := lSelectList || q'[|| ', ' || nvl(to_char(]' || i.column_name || q'[), 'null') ]';
     34                  when i.data_type = 'DATE' then
     35                     lSelectList := lSelectList || q'[|| ', ' || 'to_date('''|| to_char(]'||i.column_name||q'{, 'DDMMYYYYHH24MISS') || q'[', 'DDMMYYYYHH24MISS')]'}';
     36                  else
     37                     raise_application_error(-20001, 'Data type ' || i.data_type || ' not supported');
     38             end case;
     39     end loop;
     40     lColumnList := substr(lColumnList, 3);
     41     lSelectList := substr(lSelectList, 11);
     42     lFinalSql   := q'[ select 'insert into #owner#.#table# (#column_list#) values ('||#select_list#||');' str
     43                          from #owner#.#table#
     44                         where #where#]';
     45     lFinalSql   := replace(lFinalSql, '#table#'      , pTableName);
     46     lFinalSql   := replace(lFinalSql, '#owner#'      , pOwnerName);
     47     lFinalSql   := replace(lFinalSql, '#column_list#', lColumnList);
     48     lFinalSql   := replace(lFinalSql, '#select_list#', lSelectList);
     49     lFinalSql   := replace(lFinalSql, '#where#'      , lWhere);
     50     dbms_output.put_line(lFinalSql);
     51     open pRc for lFinalSql;
     52  end;
     53  /
     
    Procedure created.
     
    SQL> var rc refcursor
    
    SQL> exec generate_table_insert(user, 'EMP', :rc)
     
    PL/SQL procedure successfully completed.
     
    SQL> print rc
     
    STR
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7369, 'SMITH', 'CLERK', 7902, to_date('17121980000000', 'DDMMYYYYHH24MISS'), 800, 0, 20);
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20021981000000', 'DDMMYYYYHH24MISS'), 1600, 300, 30);
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7521, 'WARD', 'SALESMAN', 7698, to_date('22021981000000', 'DDMMYYYYHH24MISS'), 1250, 500, 30);
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7566, 'JONES', 'MANAGER', 7839, to_date('02041981000000', 'DDMMYYYYHH24MISS'), 2975, 0, 20);
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28091981000000', 'DDMMYYYYHH24MISS'), 1250, 1400, 30);
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01051981000000', 'DDMMYYYYHH24MISS'), 2850, 0, 30);
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7782, 'CLARK', 'MANAGER', 7839, to_date('09061981000000', 'DDMMYYYYHH24MISS'), 2450, 0, 10);
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19041987000000', 'DDMMYYYYHH24MISS'), 3000, 0, 20);
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7839, 'KING', 'PRESIDENT', null, to_date('17111981000000', 'DDMMYYYYHH24MISS'), 5000, 0, 10);
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08091981000000', 'DDMMYYYYHH24MISS'), 1500, 0, 30);
    insert into ARBORU.EMP (empno, ename, job, mgr, hiredate, sal, com, deptno) values (7876, 'ADAMS', 'CLERK', 7788, to_date('23051987000000', 'DDMMYYYYHH24MISS'), 1100, 0, 20);
     
    11 rows selected.
     
    SQL
  • 2. Re: Generate Insert statements with Dynamic Column name
    988960 Newbie
    Currently Being Moderated
    Thanks for your reply .. But I am much interested in how to concatenate/append the variable r(k). + dynamic col name ... I think with the help of dbms_sql .. But I am not able to figure it out ... Please help in this regard also
  • 3. Re: Generate Insert statements with Dynamic Column name
    BluShadow Guru Moderator
    Currently Being Moderated
    Why? What business issue are you trying to solve?

    There is often no reason to use dynamic code in a well designed database and application. When the desire for "dynamic" code arises it's usually because something has gone wrong somewhere either in the design of the database or the design of the application, or the developer is just trying to be 'clever' thinking that some 'dynamic' code will allow them to avoid having to write more code. Inevitably though it introduces lots of bugs, and security issues (including SQL injection) which may not be apparent at design time, as well as typically having an impact on the performance of the SQL being generated.

    So, step back, tell us why you feel you need to have dynamic code in the first place.
  • 4. Re: Generate Insert statements with Dynamic Column name
    988960 Newbie
    Currently Being Moderated
    I have tired to execute your code with the following anonymous block ... I got the error " Not all variables are bound" ORA-01008

    declare
    type refcur is ref cursor;
    rc refcur;
    begin
    --rc generate_table_insert.pRc;
    generate_table_insert(user, 'SMTB_CURRENT_USERS', :rc);
    end;
  • 5. Re: Generate Insert statements with Dynamic Column name
    988960 Newbie
    Currently Being Moderated
    Thanks for your reply ..

    I am just learning ... How to generate the insert scripts without mentioning the name of the column. So simple. For that I have written a piece of code which I have shared in this forum. I want dynamic code so that user will just enter the name of the table and no need to enter the cols name... dats it

    So I want to know , Is it possible to append or concatenate the variable r(k) . + col name ?
  • 6. Re: Generate Insert statements with Dynamic Column name
    988960 Newbie
    Currently Being Moderated
    The anonymous block I have tried in PL/SQL block in PL/SQL developer ..... and where are simple declaration like
    Var rc refcusror and then exec statement is working in CMD ....

    I want to know Is it possible to write pRc variable into file and How to execute this in PL/SQL developer tool . And my original question : How to append/concatenate r(k) . + dynamic col name ?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points