Home > Uncategorized > Generate data insert script as insert into

Generate data insert script as insert into

Declare
v_table_name varchar2(30) := ‘MY_TABLE’; — Your Tablename
v_column_list varchar2(4000);
v_insert_list varchar2(4000);
v_ref_cur_columns varchar2(4000);
v_ref_cur_query varchar2(4000);
v_ref_cur_output varchar2(4000);
V_Column_Name Varchar2(4000);
Cursor C1 Is Select Column_Name, Data_Type From DBA_Tab_Columns Where Table_Name = V_Table_Name Order By Column_Id;

refcur sys_refcursor;
begin
for i in c1 loop
v_column_list := v_column_list||’,’||i.column_name;
if i.data_type = ‘NUMBER’ then
v_column_name := i.column_name;
elsif i.data_type = ‘DATE’ then
v_column_name :=
chr(39)||’to_date(‘||chr(39)||’||chr(39)’||’||to_char(‘||i.column_name||’,’||chr(39)||’dd/mm/yyyy hh:mi:ss’||chr(39)||’)||chr(39)||’||chr(39)||’, ‘||chr(39)||’||chr(39)||’||chr(39)||’dd/mm/rrrr hh:mi:ss’||chr(39)||’||chr(39)||’||chr(39)||’)’||chr(39);
elsif i.data_type = ‘VARCHAR2’ then
v_column_name := ‘chr(39)||’||i.column_name||’||chr(39)’;
end if;
v_ref_cur_columns := v_ref_cur_columns||’||’||chr(39)||’,’||chr(39)||’||’||v_column_name;
end loop;
v_column_list := ltrim(v_column_list,’,’);
v_ref_cur_columns := substr(v_ref_cur_columns,8);

V_Insert_List := ‘INSERT INTO ‘||V_Table_Name||’ (‘||V_Column_List||’) VALUES ‘;
V_Ref_Cur_Query := ‘SELECT ‘||V_Ref_Cur_Columns||’ FROM ‘||V_Table_Name;

open refcur for v_ref_cur_query;
loop
fetch refcur into v_ref_cur_output;
exit when refcur%notfound;
v_ref_cur_output := ‘(‘||v_ref_cur_output||’);’;
v_ref_cur_output := replace(v_ref_cur_output,’,,’,’,null,’);
v_ref_cur_output := replace(v_ref_cur_output,'(,’,'(null,’);
v_ref_cur_output := replace(v_ref_cur_output,’,,)’,’,null)’);
v_ref_cur_output := replace(v_ref_cur_output,’null,)’,’null,null)’);
v_ref_cur_output := REPLACE(v_ref_cur_output,’,);’,’,null);’);
v_ref_cur_output := v_insert_list||v_ref_cur_output;
dbms_output.put_line (v_ref_cur_output);
end loop;
End;
/

Advertisements
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: