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