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 fn_gen_dml to do that. It generates SQL INSERT(s) per row which can be used later to load the rows. This function should be used only for small number of rows.
The function uses three parameters:
in_sql – input query to generate INSERT statements
in_new_owner_name – new owner name for generated INSERT
in_new_table_name – new table name for generated INSERT
CREATE TYPE t_clob_obj AS OBJECT
(
row_data CLOB
);
/
CREATE TYPE t_clob_tab IS TABLE of t_clob_obj
/
create or replace FUNCTION fn_gen_dml
(
in_sql CLOB,
in_new_owner_name VARCHAR2,
in_new_table_name VARCHAR2
)
RETURN t_clob_tab PIPELINED
------------------------------------------------------------------------------
-- Function : fn_gen_dml --
-- Developer : Tomasz Lesinski --
-- Date : 2019-06-08 --
-- Purpose : Generate INSERT statments for dynamic input query --
-- Prerequisite : Requires T_CLOB_OBJ, T_CLOB_TAB types --
------------------------------------------------------------------------------
--
-- Parameters:
-- in_sql - input query to generate INSERT statements
-- in_new_owner_name - new owner name for generated INSERT
-- in_new_table_name - new table name for generated INSERT
--
-- Returns:
-- type t_clob_tab - return pipelined collection of INSERT per record
-----------------------------------
IS
l_cur NUMBER;
l_sql CLOB := in_sql;
l_ret NUMBER;
l_col_cnt NUMBER;
l_rec_tab dbms_sql.desc_tab;
l_separator CHAR(1) := '!';
l_clob_all CLOB;
l_line CLOB := '-----------------------------------';
l_date_frm_const CONSTANT VARCHAR2(32) := 'DD.MM.YYYY HH24:MI:SS';
l_timestamp_frm_const CONSTANT VARCHAR2(32) := 'DD.MM.YYYY HH24:MI:SSXFF';
l_timestamp_wtz_frm_const CONSTANT VARCHAR2(32) := 'DD.MM.YYYY HH24:MI:SSXFF TZR';
l_varchar2_code_const CONSTANT NUMBER := 1;
l_nvarchar2_code_const CONSTANT NUMBER := 1;
l_number_code_const CONSTANT NUMBER := 2;
l_float_code_const CONSTANT NUMBER := 2;
l_long_code_const CONSTANT NUMBER := 8;
l_date_code_const CONSTANT NUMBER := 12;
l_binary_float_code_const CONSTANT NUMBER := 100;
l_binary_double_code_const CONSTANT NUMBER := 101;
l_timestamp_code_const CONSTANT NUMBER := 180;
l_timestamp_wtz_code_const CONSTANT NUMBER := 181;
l_timestamp_lwtz_code_const CONSTANT NUMBER := 231;
l_interval_ytm_code_const CONSTANT NUMBER := 182;
l_interval_dts_code_const CONSTANT NUMBER := 183;
l_raw_code_const CONSTANT NUMBER := 23;
l_long_raw_code_const CONSTANT NUMBER := 24;
l_rowid_code_const CONSTANT NUMBER := 11;
l_urowid_code_const CONSTANT NUMBER := 208;
l_char_code_const CONSTANT NUMBER := 96;
l_nchar_code_const CONSTANT NUMBER := 96;
l_clob_code_const CONSTANT NUMBER := 112;
l_nclob_code_const CONSTANT NUMBER := 112;
l_blob_code_const CONSTANT NUMBER := 113;
l_bfile_code_const CONSTANT NUMBER := 114;
l_xmltype_code_const CONSTANT NUMBER := 109;
l_max_literal_size_const CONSTANT NUMBER := 1000;
-------------------------------------
-- 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
l_value CLOB;
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 :=
'BEGIN ' || CHR(10) ||
' NULL; ' || 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) ||
'END;' || CHR(10) ||
'/';
--right now dont generate it
PIPE ROW(t_clob_obj(l_clob_all));
l_clob_all := null;
l_clob_all := l_clob_all || 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 = l_varchar2_code_const 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 = l_number_code_const THEN --number
dbms_sql.define_column(l_cur, i, l_number_col);
--ELSIF l_rec_tab(i).col_type = l_long_code_const then --long
-- dbms_sql.define_column_long(l_cur, i);
ELSIF l_rec_tab(i).col_type = l_date_code_const THEN --date
dbms_sql.define_column(l_cur, i, l_date_col);
ELSIF l_rec_tab(i).col_type = l_binary_float_code_const THEN --binary_float
dbms_sql.define_column(l_cur, i, l_binary_float_col);
ELSIF l_rec_tab(i).col_type = l_binary_double_code_const THEN --binary_double
dbms_sql.define_column(l_cur, i, l_binary_double_col);
-- ELSIF l_rec_tab(i).col_type = l_raw_code_const 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 = l_rowid_code_const THEN --rowid
dbms_sql.define_column_rowid(l_cur, i, l_rowid_col);
ELSIF l_rec_tab(i).col_type = l_char_code_const 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 = l_clob_code_const THEN --clob
dbms_sql.define_column(l_cur, i, l_clob_col);
ELSIF l_rec_tab(i).col_type = l_timestamp_code_const THEN --timestamp
dbms_sql.define_column(l_cur, i, l_timestamp_col);
ELSIF l_rec_tab(i).col_type = l_timestamp_wtz_code_const THEN --timestamp with time zone
dbms_sql.define_column(l_cur, i, l_timestamp_wtz_col);
ELSIF l_rec_tab(i).col_type = l_rowid_code_const THEN --urowid
dbms_sql.define_column(l_cur, i, l_urowid_col);
ELSIF l_rec_tab(i).col_type = l_timestamp_lwtz_code_const THEN --timestamp with local time zone
dbms_sql.define_column(l_cur, i, l_timestamp_wltz_col);
ELSIF l_rec_tab(i).col_type = l_interval_ytm_code_const THEN --interval year to month
dbms_sql.define_column(l_cur, i, l_interval_ytm_col);
ELSIF l_rec_tab(i).col_type = l_interval_dts_code_const THEN --interval day to second
dbms_sql.define_column(l_cur, i, l_interval_dts_col);
ELSIF l_rec_tab(i).col_type = l_urowid_code_const THEN --urowid
dbms_sql.define_column(l_cur, i, l_urowid_col);
ELSIF l_rec_tab(i).col_type = l_xmltype_code_const THEN --xmltype
dbms_sql.define_column(l_cur, i, l_xmltype_col);
ELSE --not supported type
raise_application_error(-20001, 'Column: ' || l_rec_tab(i).col_name || CHR(10) ||
'Type not supported: ' || l_rec_tab(i).col_type);
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;
l_clob_all := NULL;
---------------------------------------
-- Building INSERT - build column list
---------------------------------------
l_clob_all := l_clob_all || CHR(10) ||
'INSERT INTO ' || in_new_owner_name || '.' || in_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 - values
---------------------------------------
FOR i IN 1..l_rec_tab.count
LOOP
IF l_rec_tab(i).col_type = l_varchar2_code_const THEN --varchar2
dbms_sql.COLUMN_VALUE(l_cur, i, l_varchar2_col);
IF l_varchar2_col IS NOT NULL THEN
l_clob_all := l_clob_all || '''' || REPLACE(l_varchar2_col, '''', '''''') || ''',' || CHR(10);
ELSE
l_clob_all := l_clob_all || 'NULL,' || CHR(10);
END IF;
ELSIF l_rec_tab(i).col_type = l_number_code_const THEN --number
dbms_sql.COLUMN_VALUE(l_cur, i, l_number_col);
l_clob_all := l_clob_all || NVL(to_char(l_number_col), 'NULL') || ',' || CHR(10);
-- ELSIF l_rec_tab(i).col_type = l_long_code_const then --long
-- dbms_sql.column_value(l_cur, i, l_long_col);
-- l_clob := l_long_col;
ELSIF l_rec_tab(i).col_type = l_date_code_const THEN --date
dbms_sql.COLUMN_VALUE(l_cur, i, l_date_col);
IF l_date_col IS NOT NULL THEN
l_clob_all := l_clob_all || 'TO_DATE('''||to_char(l_date_col, l_date_frm_const) || ''',''' || l_date_frm_const || '''),' || CHR(10);
ELSE
l_clob_all := l_clob_all || 'NULL,' || CHR(10);
END IF;
ELSIF l_rec_tab(i).col_type = l_binary_float_code_const THEN --binary_float
dbms_sql.COLUMN_VALUE(l_cur, i, l_binary_float_col);
l_clob_all := l_clob_all || NVL(to_char(l_binary_float_col), 'NULL') || ',' || CHR(10);
ELSIF l_rec_tab(i).col_type = l_binary_double_code_const THEN --binary_double
dbms_sql.COLUMN_VALUE(l_cur, i, l_binary_double_col);
l_clob_all := l_clob_all || NVL(to_char(l_binary_double_col), 'NULL') || ',' || CHR(10);
-- ELSIF l_rec_tab(i).col_type = l_raw_code_const 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 = l_rowid_code_const THEN --rowid
dbms_sql.COLUMN_VALUE(l_cur, i, l_rowid_col);
l_clob_all := l_clob_all || NVL(to_char(l_rowid_col), 'NULL') || ',' || CHR(10);
ELSIF l_rec_tab(i).col_type = l_char_code_const THEN --char
dbms_sql.column_value_char(l_cur, i, l_char_col);
IF l_char_col IS NOT NULL THEN
l_clob_all := l_clob_all || '''' || REPLACE(substr(l_char_col, 1, l_rec_tab(i).col_max_len - 1), '''', '''''') || ''',' || CHR(10);
ELSE
l_clob_all := l_clob_all || 'NULL,' || CHR(10);
END IF;
ELSIF l_rec_tab(i).col_type = l_clob_code_const THEN --clob
dbms_sql.COLUMN_VALUE(l_cur, i, l_clob_col);
IF l_clob_col IS NULL OR l_clob_col = empty_clob() THEN
l_clob_all := l_clob_all || 'NULL,' || CHR(10);
ELSE
FOR j IN 1 .. ceil(LENGTH(l_clob_col)/l_max_literal_size_const)
LOOP
IF j > 1 THEN
l_clob_all := l_clob_all || CHR(10) || to_clob('||');
END IF;
l_clob_all := l_clob_all || to_clob('TO_CLOB(''') ||
replace(substr(l_clob_col, (j-1)*l_max_literal_size_const+1, l_max_literal_size_const), '''', '''''')
|| to_clob(''')');
END LOOP;
l_clob_all := l_clob_all || ',' || CHR(10);
END IF;
ELSIF l_rec_tab(i).col_type = l_timestamp_code_const THEN --timestamp
dbms_sql.COLUMN_VALUE(l_cur, i, l_timestamp_col);
IF l_timestamp_col IS NOT NULL THEN
l_clob_all := l_clob_all || 'TO_TIMESTAMP(''' || to_char(l_timestamp_col, l_timestamp_frm_const) || ''',''' || l_timestamp_frm_const || '''),' || CHR(10);
ELSE
l_clob_all := l_clob_all || 'NULL,' || CHR(10);
END IF;
ELSIF l_rec_tab(i).col_type = l_timestamp_wtz_code_const THEN --timestamp with time zone
dbms_sql.COLUMN_VALUE(l_cur, i, l_timestamp_wtz_col);
IF l_timestamp_wtz_col IS NOT NULL THEN
l_clob_all := l_clob_all || 'TO_TIMESTAMP_TZ('''||to_char(l_timestamp_wtz_col, l_timestamp_wtz_frm_const) || ''',''' || l_timestamp_wtz_frm_const || '''),' || CHR(10);
ELSE
l_clob_all := l_clob_all || 'NULL,' || CHR(10);
END IF;
ELSIF l_rec_tab(i).col_type = l_interval_ytm_code_const THEN --interval year to month
dbms_sql.COLUMN_VALUE(l_cur, i, l_interval_ytm_col);
IF l_interval_ytm_col IS NOT NULL THEN
l_clob_all := l_clob_all || 'TO_YMINTERVAL(''' || to_char(l_interval_ytm_col) || '''),' || CHR(10);
ELSE
l_clob_all := l_clob_all || 'NULL,' || CHR(10);
END IF;
ELSIF l_rec_tab(i).col_type = l_interval_dts_code_const THEN --interval day to second
dbms_sql.COLUMN_VALUE(l_cur, i, l_interval_dts_col);
IF l_interval_dts_col IS NOT NULL THEN
l_clob_all := l_clob_all || 'TO_DSINTERVAL(''' || to_char(l_interval_dts_col) || '''),' || CHR(10);
ELSE
l_clob_all := l_clob_all || 'NULL,' || CHR(10);
END IF;
ELSIF l_rec_tab(i).col_type = l_urowid_code_const THEN --urowid
dbms_sql.COLUMN_VALUE(l_cur, i, l_urowid_col);
l_clob_all := l_clob_all || NVL(to_char(l_urowid_col), 'NULL') || ',' || CHR(10);
ELSIF l_rec_tab(i).col_type = l_timestamp_lwtz_code_const THEN --timestamp with local time zone
dbms_sql.COLUMN_VALUE(l_cur, i, l_timestamp_wltz_col);
IF l_timestamp_wltz_col IS NOT NULL THEN
l_clob_all := l_clob_all || 'TO_TIMESTAMP_TZ(''' || to_char(l_timestamp_wltz_col, l_timestamp_wtz_frm_const) || ''',''' || l_timestamp_wtz_frm_const || '''),' || CHR(10);
ELSE
l_clob_all := l_clob_all || 'NULL,' || CHR(10);
END IF;
ELSIF l_rec_tab(i).col_type = l_xmltype_code_const THEN --xmltype
dbms_sql.COLUMN_VALUE(l_cur, i, l_xmltype_col);
IF l_xmltype_col IS NOT NULL THEN
FOR j IN 1 .. ceil(LENGTH(l_xmltype_col.getclobval())/l_max_literal_size_const)
LOOP
IF j > 1 THEN
l_clob_all := l_clob_all || CHR(10) || to_clob('||');
END IF;
l_clob_all := l_clob_all || to_clob('TO_CLOB(''') || replace(substr(l_xmltype_col.getclobval(), (j-1)*l_max_literal_size_const+1, l_max_literal_size_const), '''', '''''') || TO_CLOB(''')');
END LOOP;
l_clob_all := l_clob_all || ',' || CHR(10);
ELSE
l_clob_all := l_clob_all || 'NULL,' || CHR(10);
END IF;
END IF;
END LOOP;
l_clob_all := rtrim(l_clob_all, ' ,' || CHR(10)) || CHR(10) ||
');' || CHR(10);
PIPE ROW(t_clob_obj(l_clob_all));
END LOOP;
---------------------------------------
-- Close cursor
---------------------------------------
dbms_sql.close_cursor(l_cur);
RETURN;
END fn_gen_dml;
/
Let’s prepare some dummy data
CREATE TABLE test_tbl
(
id1 NUMBER,
id2 DATE,
id3 CLOB
);
INSERT INTO test_tbl
VALUES(1, sysdate, 'test
multi
line');
INSERT INTO test_tbl
VALUES(2, sysdate, 'more
data
to generate');
COMMIT;
and finally test it. Dummy header is displayed on the begining to show definitions of columns in source table.
set pagesize 0
set long 999999
SELECT *
FROM TABLE(fn_gen_dml( in_sql => 'select * from test_tbl',
in_new_owner_name => user,
in_new_table_name => 'TEST_TBL' ));
BEGIN
NULL;
/*
-----------------------------------
Parsing query:
select * from test_tbl
-----------------------------------
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 = 112
col_maxlen = 4000
col_name = ID3
col_name_len = 3
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = true
*/
END;
/
INSERT INTO TOMASZ.TEST_TBL
(
ID1
,ID2
,ID3
)
VALUES
(
1,
TO_DATE('08.06.2019 06:32:48','DD.MM.YYYY HH24:MI:SS'),
TO_CLOB('test
multi
line')
);
INSERT INTO TOMASZ.TEST_TBL
(
ID1
,ID2
,ID3
)
VALUES
(
2,
TO_DATE('08.06.2019 06:47:08','DD.MM.YYYY HH24:MI:SS'),
TO_CLOB('more
data
to generate')
);
Have a fun 🙂
 Tomasz
Any oracle object type has a type code of 109, but your code incorrectly assumes it is an XMLType. You have to also look at the type name from the DBMS_SQL type information to handle the correctly.
thanks very much for sharing such a complicated method that can be used by many Developer and DBA to make life much easier to move data from Source to Target!!
Hi,
the substr function deletes the last character, but for the Char data type. I think -1 is not needed
l_clob_all := l_clob_all || ”” || REPLACE(substr(l_char_col, 1, l_rec_tab(i).col_max_len – 1), ””, ”””) || ”’,’ || CHR(10);