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);
l_clob := substr(l_char_col, 1, l_rec_tab(i).col_max_len);
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;
/
select fn_gen_inserts('select * from USG', 'UATDB', 'USG') from dual;
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
