Oracle – generate rows as INSERT statements from table, view using PL/SQL

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

9 thoughts on “Oracle – generate rows as INSERT statements from table, view using PL/SQL

    • 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

  1. 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;

Leave a Reply

Your email address will not be published. Required fields are marked *