If you are working as developer/DBA probably you are quite often asked to migrate some rows from one database to another – usually people reference to the rows as “metadata” rows. Rows that driving your business !
If you need to prepare such rows for migrations there are a lot of methods to do it
- just copy the rows using database link from one database to another
- export/import the rows using tools like expdp/impdp
- prepare manually scripts with INSERT statements
I would like to focus on the last method. It’s very popular to keep metadata in some files as number of INSERTs for backup/migrations/versioning etc.
I decided to write my own Oracle PL/SQL function to do that. It generates PL/SQL code which can be used to load exported rows later. This function should be used only for small number of rows.
create or replace FUNCTION fn_gen_inserts ( p_sql CLOB, p_new_owner_name VARCHAR2, p_new_table_name VARCHAR2 ) RETURN CLOB IS l_cur NUMBER; l_sql CLOB := p_sql; l_ret NUMBER; l_col_cnt NUMBER; l_rec_tab dbms_sql.desc_tab; l_separator CHAR(1) := '!'; l_clob CLOB; l_clob_line CLOB; l_clob_ins CLOB; l_clob_all CLOB; l_line CLOB := '-----------------------------------'; cons_date_frm VARCHAR2(32) := 'DD.MM.YYYY HH24:MI:SS'; cons_timestamp_frm VARCHAR2(32) := 'DD.MM.YYYY HH24:MI:SSXFF'; cons_timestamp_wtz_frm VARCHAR2(32) := 'DD.MM.YYYY HH24:MI:SSXFF TZR'; cons_varchar2_code NUMBER := 1; cons_nvarchar2_code NUMBER := 1; cons_number_code NUMBER := 2; cons_float_code NUMBER := 2; cons_long_code NUMBER := 8; cons_date_code NUMBER := 12; cons_binary_float_code NUMBER := 100; cons_binary_double_code NUMBER := 101; cons_timestamp_code NUMBER := 180; cons_timestamp_wtz_code NUMBER := 181; cons_timestamp_lwtz_code NUMBER := 231; cons_interval_ytm_code NUMBER := 182; cons_interval_dts_code NUMBER := 183; cons_raw_code NUMBER := 23; cons_long_raw_code NUMBER := 24; cons_rowid_code NUMBER := 11; cons_urowid_code NUMBER := 208; cons_char_code NUMBER := 96; cons_nchar_code NUMBER := 96; cons_clob_code NUMBER := 112; cons_nclob_code NUMBER := 112; cons_blob_code NUMBER := 113; cons_bfile_code NUMBER := 114; cons_xmltype_code NUMBER := 109; ------------------------------------- -- Supported types ------------------------------------- l_varchar2_col VARCHAR2(32767); --1 l_number_col NUMBER; --2 --l_long_col long; --8 - not supported l_date_col DATE; --12 --l_raw_col raw(2000); --23 - not supported l_rowid_col ROWID; --69 l_char_col CHAR(2000); --96 l_binary_float_col BINARY_FLOAT; --100 l_binary_double_col BINARY_DOUBLE; --101 l_clob_col CLOB; --112 l_timestamp_col TIMESTAMP(9); --180 l_timestamp_wtz_col TIMESTAMP(9) WITH TIME ZONE; --181 l_interval_ytm_col INTERVAL YEAR(9) TO MONTH; --182 l_interval_dts_col INTERVAL DAY(9) TO SECOND(2); --183 l_urowid_col UROWID; --208 l_timestamp_wltz_col TIMESTAMP WITH LOCAL TIME ZONE; --231 l_xmltype_col XMLTYPE; --109 --l_nchar_col nchar(2000); --96 the same as char --l_nclob_col nclob; --112 the same as clob --l_blob_col - not supported --l_bfile_col - not supported --l_long_raw_col - not supported PROCEDURE print_rec(rec IN dbms_sql.desc_rec) IS BEGIN l_clob_all := l_clob_all||chr(10)|| 'col_type = ' || rec.col_type||chr(10)|| 'col_maxlen = ' || rec.col_max_len||chr(10)|| 'col_name = ' || rec.col_name||chr(10)|| 'col_name_len = ' || rec.col_name_len||chr(10)|| 'col_schema_name = ' || rec.col_schema_name||chr(10)|| 'col_schema_name_len = ' || rec.col_schema_name_len||chr(10)|| 'col_precision = ' || rec.col_precision||chr(10)|| 'col_scale = ' || rec.col_scale||chr(10)|| 'col_null_ok = '; IF (rec.col_null_ok) THEN l_clob_all := l_clob_all||'true'||chr(10); ELSE l_clob_all := l_clob_all||'false'||chr(10); END IF; END; BEGIN --------------------------------------- -- INSERT - header generation --------------------------------------- l_clob_all := 'declare'||chr(10)|| ' type t_clob is table of clob index by binary_integer;'||chr(10)|| ' l_clob t_clob;'||chr(10)|| ' type t_varchar2 is table of varchar2(64) index by binary_integer;'||chr(10)|| ' l_varchar2 t_varchar2;'||chr(10)|| 'begin'||chr(10)|| '/*'||chr(10); --------------------------------------- -- Introduction --------------------------------------- l_clob_all := l_clob_all||l_line||chr(10)||'Parsing query:'||chr(10)||l_sql||chr(10); --------------------------------------- -- Open parse cursor --------------------------------------- l_cur := dbms_sql.open_cursor; dbms_sql.parse(l_cur, l_sql, dbms_sql.NATIVE); --------------------------------------- -- Describe columns --------------------------------------- l_clob_all := l_clob_all||l_line||chr(10)||'Describe columns:'||chr(10); dbms_sql.describe_columns(l_cur, l_col_cnt, l_rec_tab); FOR i IN 1..l_rec_tab.count loop print_rec(l_rec_tab(i)); END loop; l_clob_all := l_clob_all||chr(10)|| '*/'||chr(10)|| ' '||chr(10)||l_line||chr(10)|| ' -- start generation of records'||chr(10)|| ' '||l_line||chr(10); --------------------------------------- -- Define columns --------------------------------------- FOR i IN 1..l_rec_tab.count loop IF l_rec_tab(i).col_type = cons_varchar2_code THEN --varchar2 dbms_sql.define_column(l_cur, i, l_varchar2_col, l_rec_tab(i).col_max_len); elsif l_rec_tab(i).col_type = cons_number_code THEN --number dbms_sql.define_column(l_cur, i, l_number_col); --elsif l_rec_tab(i).col_type = cons_long_code then --long -- dbms_sql.define_column_long(l_cur, i); elsif l_rec_tab(i).col_type = cons_date_code THEN --date dbms_sql.define_column(l_cur, i, l_date_col); elsif l_rec_tab(i).col_type = cons_binary_float_code THEN --binary_float dbms_sql.define_column(l_cur, i, l_binary_float_col); elsif l_rec_tab(i).col_type = cons_binary_double_code THEN --binary_double dbms_sql.define_column(l_cur, i, l_binary_double_col); -- elsif l_rec_tab(i).col_type = cons_raw_code then --raw -- dbms_sql.define_column_raw(l_cur, i, l_raw_col, l_rec_tab(i).col_max_len); elsif l_rec_tab(i).col_type = cons_rowid_code THEN --rowid dbms_sql.define_column_rowid(l_cur, i, l_rowid_col); elsif l_rec_tab(i).col_type = cons_char_code THEN --char dbms_sql.define_column_char(l_cur, i, l_char_col, l_rec_tab(i).col_max_len); elsif l_rec_tab(i).col_type = cons_clob_code THEN --clob dbms_sql.define_column(l_cur, i, l_clob_col); elsif l_rec_tab(i).col_type = cons_timestamp_code THEN --timestamp dbms_sql.define_column(l_cur, i, l_timestamp_col); elsif l_rec_tab(i).col_type = cons_timestamp_wtz_code THEN --timestamp with time zone dbms_sql.define_column(l_cur, i, l_timestamp_wtz_col); elsif l_rec_tab(i).col_type = cons_rowid_code THEN --urowid dbms_sql.define_column(l_cur, i, l_urowid_col); elsif l_rec_tab(i).col_type = cons_timestamp_lwtz_code THEN --timestamp with local time zone dbms_sql.define_column(l_cur, i, l_timestamp_wltz_col); elsif l_rec_tab(i).col_type = cons_interval_ytm_code THEN --interval year to month dbms_sql.define_column(l_cur, i, l_interval_ytm_col); elsif l_rec_tab(i).col_type = cons_interval_dts_code THEN --interval day to second dbms_sql.define_column(l_cur, i, l_interval_dts_col); elsif l_rec_tab(i).col_type = cons_urowid_code THEN --urowid dbms_sql.define_column(l_cur, i, l_urowid_col); elsif l_rec_tab(i).col_type = cons_xmltype_code THEN --xmltype dbms_sql.define_column(l_cur, i, l_xmltype_col); ELSE raise_application_error(-20001, 'Column: '||l_rec_tab(i).col_name||chr(10)|| 'Type not supported: '||l_rec_tab(i).col_type); --not supported END IF; END loop; --------------------------------------- -- Execute cursor --------------------------------------- l_ret := dbms_sql.EXECUTE(l_cur); --------------------------------------- -- Fetch rows --------------------------------------- loop l_ret := dbms_sql.fetch_rows(l_cur); exit WHEN l_ret = 0; --------------------------------------- -- Building INSERT - build column declarations --------------------------------------- l_clob_line := ''; FOR i IN 1..l_rec_tab.count loop IF l_rec_tab(i).col_type = cons_varchar2_code THEN --varchar2 dbms_sql.COLUMN_VALUE(l_cur, i, l_varchar2_col); l_clob := l_varchar2_col; elsif l_rec_tab(i).col_type = cons_number_code THEN --number dbms_sql.COLUMN_VALUE(l_cur, i, l_number_col); l_clob := to_char(l_number_col); -- elsif l_rec_tab(i).col_type = cons_long_code then --long -- dbms_sql.column_value(l_cur, i, l_long_col); -- l_clob := l_long_col; elsif l_rec_tab(i).col_type = cons_date_code THEN --date dbms_sql.COLUMN_VALUE(l_cur, i, l_date_col); l_clob := to_char(l_date_col, cons_date_frm); elsif l_rec_tab(i).col_type = cons_binary_float_code THEN --binary_float dbms_sql.COLUMN_VALUE(l_cur, i, l_binary_float_col); l_clob := to_char(l_binary_float_col); elsif l_rec_tab(i).col_type = cons_binary_double_code THEN --binary_double dbms_sql.COLUMN_VALUE(l_cur, i, l_binary_double_col); l_clob := to_char(l_binary_double_col); -- elsif l_rec_tab(i).col_type = cons_raw_code then --raw -- dbms_sql.column_value(l_cur, i, l_raw_col); -- l_clob := to_char(l_raw_col); elsif l_rec_tab(i).col_type = cons_rowid_code THEN --rowid dbms_sql.COLUMN_VALUE(l_cur, i, l_rowid_col); l_clob := to_char(l_rowid_col); elsif l_rec_tab(i).col_type = cons_char_code THEN --char dbms_sql.column_value_char(l_cur, i, l_char_col); l_clob := substr(l_char_col, 1, l_rec_tab(i).col_max_len - 1); elsif l_rec_tab(i).col_type = cons_clob_code THEN --clob dbms_sql.COLUMN_VALUE(l_cur, i, l_clob_col); l_clob := l_clob_col; elsif l_rec_tab(i).col_type = cons_timestamp_code THEN --timestamp dbms_sql.COLUMN_VALUE(l_cur, i, l_timestamp_col); l_clob := to_char(l_timestamp_col, cons_timestamp_frm); elsif l_rec_tab(i).col_type = cons_timestamp_wtz_code THEN --timestamp with time zone dbms_sql.COLUMN_VALUE(l_cur, i, l_timestamp_wtz_col); l_clob := to_char(l_timestamp_wtz_col, cons_timestamp_wtz_frm); elsif l_rec_tab(i).col_type = cons_interval_ytm_code THEN --interval year to month dbms_sql.COLUMN_VALUE(l_cur, i, l_interval_ytm_col); l_clob := to_char(l_interval_ytm_col); elsif l_rec_tab(i).col_type = cons_interval_dts_code THEN --interval day to second dbms_sql.COLUMN_VALUE(l_cur, i, l_interval_dts_col); l_clob := to_char(l_interval_dts_col); elsif l_rec_tab(i).col_type = cons_urowid_code THEN --urowid dbms_sql.COLUMN_VALUE(l_cur, i, l_urowid_col); l_clob := to_char(l_urowid_col); elsif l_rec_tab(i).col_type = cons_timestamp_lwtz_code THEN --timestamp with local time zone dbms_sql.COLUMN_VALUE(l_cur, i, l_timestamp_wltz_col); l_clob := to_char(l_timestamp_wltz_col, cons_timestamp_wtz_frm); elsif l_rec_tab(i).col_type = cons_xmltype_code THEN --xmltype dbms_sql.COLUMN_VALUE(l_cur, i, l_xmltype_col); l_clob := l_xmltype_col.getclobval(); END IF; IF l_rec_tab(i).col_type IN (cons_clob_code, cons_char_code, cons_varchar2_code) THEN l_clob_line := l_clob_line||' l_clob('||to_char(i)||') :=q'''||l_separator||l_clob||l_separator||''';'||chr(10); ELSIF l_rec_tab(i).col_type IN (cons_xmltype_code) THEN l_clob_line := l_clob_line||' l_clob('||to_char(i)||') :=q'''||l_separator||l_clob||l_separator||''';'||chr(10); ELSE l_clob_line := l_clob_line||' l_varchar2('||to_char(i)||') :=q'''||l_separator||l_clob||l_separator||''';'||chr(10); END IF; END loop; l_clob_all := l_clob_all||chr(10)||l_clob_line; --------------------------------------- -- Building INSERT - build column list --------------------------------------- l_clob_all := l_clob_all||chr(10)|| ' insert into '||p_new_owner_name||'.'||p_new_table_name||chr(10)|| ' ('||chr(10); FOR i IN 1..l_rec_tab.count loop IF i = 1 THEN l_clob_all := l_clob_all||' '||l_rec_tab(i).col_name||chr(10); ELSE l_clob_all := l_clob_all||' ,'||l_rec_tab(i).col_name||chr(10); END IF; END loop; l_clob_all := l_clob_all|| ' )'||chr(10)|| ' values'||chr(10)|| ' ('||chr(10); --------------------------------------- -- Building INSERT - build values --------------------------------------- FOR i IN 1..l_rec_tab.count loop IF i!=1 THEN l_clob_all := l_clob_all||' ,'; ELSE l_clob_all := l_clob_all||' '; END IF; IF l_rec_tab(i).col_type = cons_number_code THEN --number l_clob_all := l_clob_all||'to_number(l_varchar2('||to_char(i)||'))'||chr(10); -- elsif l_rec_tab(i).col_type = cons_long_code then --long -- l_clob := l_long_col; elsif l_rec_tab(i).col_type = cons_clob_code THEN --clob, xmltype l_clob_all := l_clob_all||'l_clob('||to_char(i)||')'||chr(10); ELSIF L_REC_TAB(I).COL_TYPE = CONS_XMLTYPE_CODE then --clob, xmltype l_clob_all := l_clob_all||'xmltype(l_clob('||to_char(i)||'))'||chr(10); elsif l_rec_tab(i).col_type = cons_char_code THEN --timestamp with local time zone l_clob_all := l_clob_all||'to_char(l_clob('||to_char(i)||'))'||chr(10); elsif l_rec_tab(i).col_type = cons_varchar2_code THEN --timestamp with local time zone l_clob_all := l_clob_all||'to_char(l_clob('||to_char(i)||'))'||chr(10); elsif l_rec_tab(i).col_type = cons_date_code THEN --date l_clob_all := l_clob_all||'to_date(l_varchar2('||to_char(i)||'),'''||cons_date_frm||''')'||chr(10); elsif l_rec_tab(i).col_type = cons_timestamp_code THEN --timestamp l_clob_all := l_clob_all||'to_timestamp(l_varchar2('||to_char(i)||'),'''||cons_timestamp_frm||''')'||chr(10); elsif l_rec_tab(i).col_type = cons_timestamp_wtz_code THEN --timestamp with time zone l_clob_all := l_clob_all||'to_timestamp_tz(l_varchar2('||to_char(i)||'),'''||cons_timestamp_wtz_frm||''')'||chr(10); elsif l_rec_tab(i).col_type = cons_interval_ytm_code THEN --interval year to month l_clob_all := l_clob_all||'to_yminterval(l_varchar2('||to_char(i)||'))'||chr(10); elsif l_rec_tab(i).col_type = cons_interval_dts_code THEN --interval day to second l_clob_all := l_clob_all||'to_dsinterval(l_varchar2('||to_char(i)||'))'||chr(10); elsif l_rec_tab(i).col_type = cons_timestamp_lwtz_code THEN --timestamp with local time zone l_clob_all := l_clob_all||'to_timestamp_tz(l_varchar2('||to_char(i)||'),'''||cons_timestamp_wtz_frm||''')'||chr(10); ELSE l_clob_all := l_clob_all||'l_varchar2('||to_char(i)||')'||chr(10); END IF; END loop; l_clob_all := l_clob_all||' );'||chr(10); END loop; --------------------------------------- -- Building INSERT - end of code --------------------------------------- l_clob_all := l_clob_all||chr(10)||'end;'; l_clob_all := l_clob_all||chr(10)||'/'; --------------------------------------- -- Close cursor --------------------------------------- dbms_sql.close_cursor(l_cur); RETURN l_clob_all; END; /
the function has got three parameters
- p_sql – dynamic query which will be used to export metadata rows
- p_new_owner_name – owner name which will be used for generated INSERT
- p_new_table_name – table name which will be used for generated INSERT
and returns generated INSERT(s) as clob
It supports following Oracle types
- char
- varchar2
- clob
- number
- binary_float
- binary_double
- date
- rowid
- urowid
- timestamp
- timestamp with time zone
- timestamp with local time zone
- interval year to month
- interval day to second
- xmltype
Let’s prepare some data
create table test_src ( id1 number, id2 date, id3 timestamp, id4 varchar2(10), id5 clob ); insert into test_src values ( 1.21, sysdate, systimestamp, 'rec1', 'test clob 1' ); insert into test_src values ( 2.42, sysdate, systimestamp, 'rec2', 'test clob 2' ); commit;
to generate INSERT(s) for new table TEST_DST just execute function. It will return CLOB with code which can be executed later.
select fn_gen_inserts('select * from test_src', 'TOMASZ', 'TEST_DST') from dual;
Here is returned code from above function
declare type t_clob is table of clob index by binary_integer; l_clob t_clob; type t_varchar2 is table of varchar2(64) index by binary_integer; l_varchar2 t_varchar2; begin /* ----------------------------------- Parsing query: select * from test_src ----------------------------------- Describe columns: col_type = 2 col_maxlen = 22 col_name = ID1 col_name_len = 3 col_schema_name = col_schema_name_len = 0 col_precision = 0 col_scale = -127 col_null_ok = true col_type = 12 col_maxlen = 7 col_name = ID2 col_name_len = 3 col_schema_name = col_schema_name_len = 0 col_precision = 0 col_scale = 0 col_null_ok = true col_type = 180 col_maxlen = 11 col_name = ID3 col_name_len = 3 col_schema_name = col_schema_name_len = 0 col_precision = 0 col_scale = 6 col_null_ok = true col_type = 1 col_maxlen = 10 col_name = ID4 col_name_len = 3 col_schema_name = col_schema_name_len = 0 col_precision = 0 col_scale = 0 col_null_ok = true col_type = 112 col_maxlen = 4000 col_name = ID5 col_name_len = 3 col_schema_name = col_schema_name_len = 0 col_precision = 0 col_scale = 0 col_null_ok = true */ ----------------------------------- -- start generation of records ----------------------------------- l_varchar2(1) :=q'!1,21!'; l_varchar2(2) :=q'!15.12.2013 01:05:22!'; l_varchar2(3) :=q'!15.12.2013 01:05:22,391000000!'; l_clob(4) :=q'!rec1!'; l_clob(5) :=q'!test clob 1!'; insert into TOMASZ.TEST_DST ( ID1 ,ID2 ,ID3 ,ID4 ,ID5 ) values ( to_number(l_varchar2(1)) ,to_date(l_varchar2(2),'DD.MM.YYYY HH24:MI:SS') ,to_timestamp(l_varchar2(3),'DD.MM.YYYY HH24:MI:SSXFF') ,to_char(l_clob(4)) ,l_clob(5) ); l_varchar2(1) :=q'!2,42!'; l_varchar2(2) :=q'!15.12.2013 01:05:22!'; l_varchar2(3) :=q'!15.12.2013 01:05:22,407000000!'; l_clob(4) :=q'!rec2!'; l_clob(5) :=q'!test clob 2!'; insert into TOMASZ.TEST_DST ( ID1 ,ID2 ,ID3 ,ID4 ,ID5 ) values ( to_number(l_varchar2(1)) ,to_date(l_varchar2(2),'DD.MM.YYYY HH24:MI:SS') ,to_timestamp(l_varchar2(3),'DD.MM.YYYY HH24:MI:SSXFF') ,to_char(l_clob(4)) ,l_clob(5) ); end; /
Have a fun 🙂
Tomasz
Thank you very much for the script ,script helped me a lot .
Great 🙂
Hi this is very useful.
But i need to pass only one parameter like only the table name.
Please do the needful
You sholud write simple wrapper around function fn_gen_inserts
fn_gen_inserts(‘select * from test_src’, ‘TOMASZ’, ‘TEST_DST’)
like this
create function fn_gen_inserts_wrp(in_tbl_name varchar2)
return clob
is
begin
return fn_gen_inserts(‘select * from ‘||in_tbl_name, user, in_tbl_name);
end;
/
Cheers
Tomasz
Hi this is very useful.tanks a lot for this topic
But i need update statements from table
can you help me?
Please do the needful
Maybe I’ll do it :o) one day. Try to do it yourself it’s very simple.
Regards
Tomasz
Thank you very much.
this subject is very important for me
i can’t create “WHERE” clause, because my PK fields in table are unknown
My problem is just the “WHERE” clause.
i write this script for update
create or replace function fn_gen_update(p_sql clob,
p_new_table_name varchar2,
p_new_owner_name varchar2 default null)
return clob is
l_cur number;
NL varchar2(2) := chr(13) || chr(10);
l_sql clob := p_sql;
l_ret number;
l_col_cnt number;
l_rec_tab sys.dbms_sql.desc_tab;
l_separator char(1) := ‘!’;
l_clob clob;
l_clob_line clob;
l_clob_ins clob;
l_clob_all clob;
l_WhereClause_all clob;
l_WhereClause_Single clob;
l_line clob := ‘———————————–‘;
cons_date_frm varchar2(32) := ‘DD.MM.YYYY HH24:MI:SS’;
cons_timestamp_frm varchar2(32) := ‘DD.MM.YYYY HH24:MI:SSXFF’;
cons_timestamp_wtz_frm varchar2(32) := ‘DD.MM.YYYY HH24:MI:SSXFF TZR’;
cons_varchar2_code number := 1;
cons_nvarchar2_code number := 1;
cons_number_code number := 2;
cons_float_code number := 2;
cons_long_code number := 8;
cons_date_code number := 12;
cons_binary_float_code number := 100;
cons_binary_double_code number := 101;
cons_timestamp_code number := 180;
cons_timestamp_wtz_code number := 181;
cons_timestamp_lwtz_code number := 231;
cons_interval_ytm_code number := 182;
cons_interval_dts_code number := 183;
cons_raw_code number := 23;
cons_long_raw_code number := 24;
cons_rowid_code number := 11;
cons_urowid_code number := 208;
cons_char_code number := 96;
cons_nchar_code number := 96;
cons_clob_code number := 112;
cons_nclob_code number := 112;
cons_blob_code number := 113;
cons_bfile_code number := 114;
————————————-
— Supported types
————————————-
l_varchar2_col varchar2(32767); –1
l_number_col number; –2
–l_long_col long; –8 – not supported
l_date_col date; –12
–l_raw_col raw(2000); –23 – not supported
l_rowid_col rowid; –69
l_char_col char(2000); –96
l_binary_float_col binary_float; –100
l_binary_double_col binary_double; –101
l_clob_col clob; –112
l_timestamp_col timestamp(9); –180
l_timestamp_wtz_col timestamp(9)
with time zone; –181
l_interval_ytm_col interval year(9) to month; –182
l_interval_dts_col interval day(9) to second(2); –183
l_urowid_col urowid; –208
l_timestamp_wltz_col timestamp
with local time zone; –231
–l_nchar_col nchar(2000); –96 the same as char
–l_nclob_col nclob; –112 the same as clob
–l_blob_col – not supported
–l_bfile_col – not supported
–l_long_raw_col – not supported
procedure print_rec(rec in sys.dbms_sql.desc_rec) is
begin
l_clob_all := l_clob_all || NL || ‘col_type = ‘ ||
rec.col_type || NL || ‘col_maxlen = ‘ ||
rec.col_max_len || NL || ‘col_name = ‘ ||
rec.col_name || NL || ‘col_name_len = ‘ ||
rec.col_name_len || NL || ‘col_schema_name = ‘ ||
rec.col_schema_name || NL || ‘col_schema_name_len = ‘ ||
rec.col_schema_name_len || NL ||
‘col_precision = ‘ || rec.col_precision || NL ||
‘col_scale = ‘ || rec.col_scale || NL ||
‘col_null_ok = ‘;
if (rec.col_null_ok) then
l_clob_all := l_clob_all || ‘true’ || NL;
else
l_clob_all := l_clob_all || ‘false’ || NL;
end if;
end;
begin
—————————————
— INSERT – header generation
—————————————
l_clob_all :=–‘set define off’||NL||
‘declare’ || NL ||
‘ type t_clob is table of clob index by binary_integer;’ || NL ||
‘ l_clob t_clob;’ || NL ||
‘ type t_varchar2 is table of varchar2(64) index by binary_integer;’ || NL ||
‘ l_varchar2 t_varchar2;’ || NL || ‘begin’ || NL;
—————————————
— Introduction
—————————————
— l_clob_all := l_clob_all||l_line||NL||’Parsing query:’||NL||l_sql||NL;
—————————————
— Open parse cursor
—————————————
l_cur := sys.dbms_sql.open_cursor;
sys.dbms_sql.parse(l_cur, l_sql, sys.dbms_sql.native);
—————————————
— Describe columns
—————————————
sys.dbms_sql.describe_columns(l_cur, l_col_cnt, l_rec_tab);
/*
l_clob_all := l_clob_all||l_line||NL||’Describe columns:’||NL;
for i in 1..l_rec_tab.count
loop
print_rec(l_rec_tab(i));
end loop;
*/
l_clob_all := l_clob_all || NL || ‘ null;’ || NL ||
‘ — start generation of records’ || NL || ‘ ‘ || l_line || NL;
—————————————
— Define columns
—————————————
for i in 1 .. l_rec_tab.count loop
if l_rec_tab(i).col_type = cons_varchar2_code then
–varchar2
sys.dbms_sql.define_column(l_cur,
i,
l_varchar2_col,
l_rec_tab(i).col_max_len);
elsif l_rec_tab(i).col_type = cons_number_code then
–number
sys.dbms_sql.define_column(l_cur, i, l_number_col);
–elsif l_rec_tab(i).col_type = cons_long_code then –long
— sys.dbms_sql.define_column_long(l_cur, i);
elsif l_rec_tab(i).col_type = cons_date_code then
–date
sys.dbms_sql.define_column(l_cur, i, l_date_col);
elsif l_rec_tab(i).col_type = cons_binary_float_code then
–binary_float
sys.dbms_sql.define_column(l_cur, i, l_binary_float_col);
elsif l_rec_tab(i).col_type = cons_binary_double_code then
–binary_double
sys.dbms_sql.define_column(l_cur, i, l_binary_double_col);
— elsif l_rec_tab(i).col_type = cons_raw_code then –raw
— sys.dbms_sql.define_column_raw(l_cur, i, l_raw_col, l_rec_tab(i).col_max_len);
elsif l_rec_tab(i).col_type = cons_rowid_code then
–rowid
sys.dbms_sql.define_column_rowid(l_cur, i, l_rowid_col);
elsif l_rec_tab(i).col_type = cons_char_code then
–char
sys.dbms_sql.define_column_char(l_cur,
i,
l_char_col,
l_rec_tab(i).col_max_len);
elsif l_rec_tab(i).col_type = cons_clob_code then
–clob
sys.dbms_sql.define_column(l_cur, i, l_clob_col);
elsif l_rec_tab(i).col_type = cons_timestamp_code then
–timestamp
sys.dbms_sql.define_column(l_cur, i, l_timestamp_col);
elsif l_rec_tab(i).col_type = cons_timestamp_wtz_code then
–timestamp with time zone
sys.dbms_sql.define_column(l_cur, i, l_timestamp_wtz_col);
elsif l_rec_tab(i).col_type = cons_rowid_code then
–urowid
sys.dbms_sql.define_column(l_cur, i, l_urowid_col);
elsif l_rec_tab(i).col_type = cons_timestamp_lwtz_code then
–timestamp with local time zone
sys.dbms_sql.define_column(l_cur, i, l_timestamp_wltz_col);
elsif l_rec_tab(i).col_type = cons_interval_ytm_code then
–interval year to month
sys.dbms_sql.define_column(l_cur, i, l_interval_ytm_col);
elsif l_rec_tab(i).col_type = cons_interval_dts_code then
–interval day to second
sys.dbms_sql.define_column(l_cur, i, l_interval_dts_col);
elsif l_rec_tab(i).col_type = cons_urowid_code then
–urowid
sys.dbms_sql.define_column(l_cur, i, l_urowid_col);
else
raise_application_error(-20001,
‘Column: ‘ || l_rec_tab(i).col_name || NL ||
‘Type not supported: ‘ || l_rec_tab(i)
.col_type);
–not supported
end if;
end loop;
—————————————
— Execute cursor
—————————————
l_ret := sys.dbms_sql.execute(l_cur);
—————————————
— Fetch rows
—————————————
loop
l_ret := sys.dbms_sql.fetch_rows(l_cur);
exit when l_ret = 0;
—————————————
— Building INSERT – build column declarations
—————————————
l_clob_line := ”;
for i in 1 .. l_rec_tab.count loop
if l_rec_tab(i).col_type = cons_varchar2_code then
–varchar2
sys.dbms_sql.column_value(l_cur, i, l_varchar2_col);
l_clob := l_varchar2_col;
elsif l_rec_tab(i).col_type = cons_number_code then
–number
sys.dbms_sql.column_value(l_cur, i, l_number_col);
l_clob := to_char(l_number_col);
— elsif l_rec_tab(i).col_type = cons_long_code then –long
— sys.dbms_sql.column_value(l_cur, i, l_long_col);
— l_clob := l_long_col;
elsif l_rec_tab(i).col_type = cons_date_code then
–date
sys.dbms_sql.column_value(l_cur, i, l_date_col);
l_clob := to_char(l_date_col, cons_date_frm);
elsif l_rec_tab(i).col_type = cons_binary_float_code then
–binary_float
sys.dbms_sql.column_value(l_cur, i, l_binary_float_col);
l_clob := to_char(l_binary_float_col);
elsif l_rec_tab(i).col_type = cons_binary_double_code then
–binary_double
sys.dbms_sql.column_value(l_cur, i, l_binary_double_col);
l_clob := to_char(l_binary_double_col);
— elsif l_rec_tab(i).col_type = cons_raw_code then –raw
— sys.dbms_sql.column_value(l_cur, i, l_raw_col);
— l_clob := to_char(l_raw_col);
elsif l_rec_tab(i).col_type = cons_rowid_code then
–rowid
sys.dbms_sql.column_value(l_cur, i, l_rowid_col);
l_clob := to_char(l_rowid_col);
elsif l_rec_tab(i).col_type = cons_char_code then
–char
sys.dbms_sql.column_value_char(l_cur, i, l_char_col);
l_clob := substr(l_char_col, 1, l_rec_tab(i).col_max_len – 1);
elsif l_rec_tab(i).col_type = cons_clob_code then
–clob
sys.dbms_sql.column_value(l_cur, i, l_clob_col);
l_clob := l_clob_col;
elsif l_rec_tab(i).col_type = cons_timestamp_code then
–timestamp
sys.dbms_sql.column_value(l_cur, i, l_timestamp_col);
l_clob := to_char(l_timestamp_col, cons_timestamp_frm);
elsif l_rec_tab(i).col_type = cons_timestamp_wtz_code then
–timestamp with time zone
sys.dbms_sql.column_value(l_cur, i, l_timestamp_wtz_col);
l_clob := to_char(l_timestamp_wtz_col, cons_timestamp_wtz_frm);
elsif l_rec_tab(i).col_type = cons_interval_ytm_code then
–interval year to month
sys.dbms_sql.column_value(l_cur, i, l_interval_ytm_col);
l_clob := to_char(l_interval_ytm_col);
elsif l_rec_tab(i).col_type = cons_interval_dts_code then
–interval day to second
sys.dbms_sql.column_value(l_cur, i, l_interval_dts_col);
l_clob := to_char(l_interval_dts_col);
elsif l_rec_tab(i).col_type = cons_urowid_code then
–urowid
sys.dbms_sql.column_value(l_cur, i, l_urowid_col);
l_clob := to_char(l_urowid_col);
elsif l_rec_tab(i).col_type = cons_timestamp_lwtz_code then
–timestamp with local time zone
sys.dbms_sql.column_value(l_cur, i, l_timestamp_wltz_col);
l_clob := to_char(l_timestamp_wltz_col, cons_timestamp_wtz_frm);
end if;
if l_rec_tab(i)
.col_type in (cons_clob_code, cons_char_code, cons_varchar2_code) then
l_clob_line := l_clob_line || ‘ l_clob(‘ || to_char(i) ||
‘) :=q”’ || l_separator || l_clob || l_separator ||
”’;’ || NL;
else
l_clob_line := l_clob_line || ‘ l_varchar2(‘ || to_char(i) ||
‘) :=q”’ || l_separator || l_clob || l_separator ||
”’;’ || NL;
end if;
end loop;
l_clob_all := l_clob_all || NL || l_clob_line;
—————————————
— Building INSERT – build column list
—————————————
if p_new_owner_name is null then
l_clob_all := l_clob_all || chr(13) || NL || ‘ update ‘ ||
p_new_table_name || NL || ‘ set ‘ || NL;
else
l_clob_all := l_clob_all || chr(13) || NL || ‘ update ‘ ||
p_new_owner_name || ‘.’ || p_new_table_name || NL ||
‘ set ‘ || NL;
end if;
/* for i in 1..l_rec_tab.count
loop
if i = 1 then
l_clob_all := l_clob_all||’ ‘||'”‘||l_rec_tab(i).col_name||'”‘||NL;
else
l_clob_all := l_clob_all||’ ,’||'”‘||l_rec_tab(i).col_name||'”‘||NL;
end if;
end loop;*/
/* l_clob_all := l_clob_all||
‘ )’||NL||
‘ values’||NL||
‘ (‘||NL;*/
—————————————
— Building update – build values
—————————————
l_WhereClause_all:=”;
for i in 1 .. l_rec_tab.count loop
if i != 1 then
l_clob_all := l_clob_all || ‘ , ‘;
else
l_clob_all := l_clob_all || ‘ ‘;
end if;
if l_rec_tab(i).col_type = cons_number_code then
–number
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_number(l_varchar2(‘ || to_char(i) || ‘))’ || NL;
l_WhereClause_Single:= ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_number(l_varchar2(‘ || to_char(i) || ‘))’ || NL;
— elsif l_rec_tab(i).col_type = cons_long_code then –long
— l_clob := l_long_col;
elsif l_rec_tab(i).col_type = cons_clob_code then
–clob
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘l_clob(‘ || to_char(i) || ‘)’ || NL;
l_WhereClause_Single:='”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘l_clob(‘ || to_char(i) || ‘)’ || NL;
elsif l_rec_tab(i).col_type = cons_char_code then
–timestamp with local time zone
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_char(l_clob(‘ || to_char(i) || ‘))’ || NL;
l_WhereClause_Single:='”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_char(l_clob(‘ || to_char(i) || ‘))’ || NL;
elsif l_rec_tab(i).col_type = cons_varchar2_code then
–timestamp with local time zone
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_char(l_clob(‘ || to_char(i) || ‘))’ || NL;
l_WhereClause_Single:='”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_char(l_clob(‘ || to_char(i) || ‘))’ || NL;
elsif l_rec_tab(i).col_type = cons_date_code then
–date
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_date(l_varchar2(‘ || to_char(i) || ‘),”’ ||
cons_date_frm || ”’)’ || NL;
l_WhereClause_Single:='”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_date(l_varchar2(‘ || to_char(i) || ‘),”’ ||
cons_date_frm || ”’)’ || NL;
elsif l_rec_tab(i).col_type = cons_timestamp_code then
–timestamp
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_timestamp(l_varchar2(‘ || to_char(i) || ‘),”’ ||
cons_timestamp_frm || ”’)’ || NL;
l_WhereClause_Single:='”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_timestamp(l_varchar2(‘ || to_char(i) || ‘),”’ ||
cons_timestamp_frm || ”’)’ || NL;
elsif l_rec_tab(i).col_type = cons_timestamp_wtz_code then
–timestamp with time zone
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_timestamp_tz(l_varchar2(‘ || to_char(i) || ‘),”’ ||
cons_timestamp_wtz_frm || ”’)’ || NL;
l_WhereClause_Single:='”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_timestamp_tz(l_varchar2(‘ || to_char(i) || ‘),”’ ||
cons_timestamp_wtz_frm || ”’)’ || NL;
elsif l_rec_tab(i).col_type = cons_interval_ytm_code then
–interval year to month
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_yminterval(l_varchar2(‘ || to_char(i) || ‘))’ || NL;
l_WhereClause_Single:='”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_yminterval(l_varchar2(‘ || to_char(i) || ‘))’ || NL;
elsif l_rec_tab(i).col_type = cons_interval_dts_code then
–interval day to second
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_dsinterval(l_varchar2(‘ || to_char(i) || ‘))’ || NL;
l_WhereClause_Single:='”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_dsinterval(l_varchar2(‘ || to_char(i) || ‘))’ || NL;
elsif l_rec_tab(i).col_type = cons_timestamp_lwtz_code then
–timestamp with local time zone
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_timestamp_tz(l_varchar2(‘ || to_char(i) || ‘),”’ ||
cons_timestamp_wtz_frm || ”’)’ || NL;
l_WhereClause_Single:='”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘to_timestamp_tz(l_varchar2(‘ || to_char(i) || ‘),”’ ||
cons_timestamp_wtz_frm || ”’)’ || NL;
else
l_clob_all := l_clob_all || ‘”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘l_varchar2(‘ || to_char(i) || ‘)’ || NL;
l_WhereClause_Single:='”‘ || l_rec_tab(i).col_name || ‘”=’ || NL ||
‘l_varchar2(‘ || to_char(i) || ‘)’ || NL;
end if;
for ipk in (SELECT c.COLUMN_NAME
FROM all_constraints t
inner join all_cons_columns c
on t.CONSTRAINT_NAME = c.CONSTRAINT_NAME
where t.CONSTRAINT_TYPE = ‘P’
and c.table_name = p_new_table_name) loop
if ipk.column_name = l_rec_tab(i).col_name then
l_WhereClause_all := l_WhereClause_all || ‘ and ‘;
l_WhereClause_all := l_WhereClause_all || ‘ ‘||l_WhereClause_Single;
l_WhereClause_Single:=”;
end if;
end loop;
end loop;
l_WhereClause_all:= substr(l_WhereClause_all,5,length(l_WhereClause_all));
l_clob_all := l_clob_all || ‘ where ‘ || NL || l_WhereClause_all|| ‘;’;
end loop;
—————————————
— Building INSERT – end of code
—————————————
l_clob_all := l_clob_all || NL;
l_clob_all := l_clob_all || ‘end;’ || NL;
— l_clob_all := l_clob_all||’x’;
—————————————
— Close cursor
—————————————
sys.dbms_sql.close_cursor(l_cur);
l_clob_all := l_clob_all || ” || NL;
return trim(l_clob_all);
end;