6 Replies Latest reply: Feb 7, 2013 8:22 AM by 988960 RSS

    Generate Insert statements with Dynamic Column name

    988960
      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_
          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
            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
              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
                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
                  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
                    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 ?