This discussion is archived
3 Replies Latest reply: Feb 6, 2013 11:41 PM by 988960 RSS

Generate Insert statements with Dynamic Column name

988960 Newbie
Currently Being Moderated
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;

Legend

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