3 Replies Latest reply: Feb 7, 2013 1:41 AM by 988960 RSS

    Generate Insert statements with Dynamic Column name

    988960
      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;