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

Posted on December 15, 2013 by joda3008

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

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

vijay on March 19, 2014 at 3:37 pm said:

Thank you very much for the script ,script helped me a lot .

Reply ↓
- admin on March 19, 2014 at 3:44 pm said:
  
  Great 🙂
  
  Reply ↓
Anu on August 22, 2014 at 9:48 am said:

Hi this is very useful.
But i need to pass only one parameter like only the table name.
Please do the needful

Reply ↓
- admin on August 22, 2014 at 2:58 pm said:
  
  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
  
  Reply ↓
shahed on September 26, 2015 at 6:08 am said:

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

Reply ↓
- admin on September 26, 2015 at 10:12 pm said:
  
  Maybe I’ll do it :o) one day. Try to do it yourself it’s very simple.
  
  Regards
  Tomasz
  
  Reply ↓
  - shahed on September 27, 2015 at 6:07 am said:
    
    Thank you very much.
    this subject is very important for me
    
    Reply ↓
  - shahed on September 27, 2015 at 6:43 am said:
    
    i can’t create “WHERE” clause, because my PK fields in table are unknown
    My problem is just the “WHERE” clause.
    
    Reply ↓
shahed on September 30, 2015 at 7:09 am said:

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;

Reply ↓
Kiran Beeharry on April 18, 2021 at 9:11 am said:

Dear Sir,
Thanks For Your Dedicated Time And Hard Work To Write The Function Which Is Very Instructive. I Really Appreciate It. Thanks Again.
However, I Have My Own Simple Sql Script To Generate INSERT Statements As Listed Below. Before Running Your INSERT Statements, SET DEFINE OFF; And After Running Them COMMIT; SET DEFINE ON;
Note : Single Quote, & sign, And / sign Within A String Value Give Problem. DEFINE Resolves The & Sign Issue. REPLACE Resolves Single Quote And ‘/’ Issues. Below Is A Simple Example. Try To Generate Your INSERT Statements Following The Below Example. Hopefully It Will Work.

SELECT ‘INSERT INTO IMP_EXP_CONSIGS(IEC_CODE,IEC_NAME1,IEC_NAME2,IEC_ADR1,IEC_ADR2,IEC_ADR3,IEC_OCC_FLAG,IEC_NID,IEC_PASSPORT_NO,’||
‘IEC_BRN,IEC_VAT,IEC_IES_CODE,IEC_CTY_CODE,IEC_BUS_DESC,IEC_PH_NO,IEC_MB_NO,IEC_EMAIL,’||
‘IEC_COMPLIANT_FLAG,IEC_COMPLIANT_REM,IEC_ACT_STATUS,’||
‘IEC_USER_ID,IEC_DATE,IEC_DT_LAST_CHANGED) VALUES (”’||
IEC_CODE||”’,”’||
REPLACE(REPLACE(IEC_NAME1,””,”””),’\’,’\\’)||”’,”’||
REPLACE(REPLACE(IEC_NAME2,””,”””),’\’,’\\’)||”’,”’||
REPLACE(REPLACE(IEC_ADDRESS1,””,”””),’\’,’\\’)||”’,”’||
REPLACE(REPLACE(IEC_ADDRESS2,””,”””),’\’,’\\’)||”’,”’||
REPLACE(REPLACE(IEC_ADDRESS3,””,”””),’\’,’\\’)||”’,”’||
NVL(IEC_OCC_FLAG,’N’)||”’,”’||
IEC_ID||”’,”’||
”||”’,”’||
IEC_BRN||”’,”’||
”||”’,”’||
‘LIN’||”’,”’||
NVL(IEC_NATIONALITY,’MU’)||”’,”’||
REPLACE(REPLACE(IEC_BUSINESS_LINE,””,”””),’\’,’\\’)||”’,”’||
IEC_TEL||”’,”’||
IEC_MOBILE_NO||”’,”’||
IEC_EMAIL||”’,”’||
NVL(IEC_COMPLIANT_FLAG,’Y’)||”’,”’||
”||”’,”’||
NVL(IEC_ACT_STATUS,’Y’)||”’,”TAES”,SYSDATE,SYSDATE);’
FROM IMP_EXP_CONSIGS
WHERE TRUNC(SYSDATE) BETWEEN TRUNC(IEC_DT_FROM) AND TRUNC(IEC_DT_TO);

Reply ↓
Kiran Beeharry on April 18, 2021 at 9:18 am said:

Dear Sir,
Thanks For Your Dedicated Time And Hard Work To Write The Function Which Is Very Instructive. I Really Appreciate It. Thanks Again.
However, I Have My Own Simple Sql Script To Generate INSERT Statements As Listed Below. Before Running Your INSERT Statements, SET DEFINE OFF; And After Running Them COMMIT; SET DEFINE ON;
Note : Single Quote, & sign, And / sign Within A String Value Give Problem. DEFINE Resolves The & Sign Issue. REPLACE Resolves Single Quote And ‘/’ Issues. Below Is A Simple Example. Try To Generate Your INSERT Statements Following The Below Example. Hopefully It Will Work. Sorry There Was A Mistake In Previous Post. ‘\’ Should Be ‘/’. See Below.

SELECT ‘INSERT INTO IMP_EXP_CONSIGS(IEC_CODE,IEC_NAME1,IEC_NAME2,IEC_ADR1,IEC_ADR2,IEC_ADR3,IEC_OCC_FLAG,IEC_NID,IEC_PASSPORT_NO,’||
‘IEC_BRN,IEC_VAT,IEC_IES_CODE,IEC_CTY_CODE,IEC_BUS_DESC,IEC_PH_NO,IEC_MB_NO,IEC_EMAIL,’||
‘IEC_COMPLIANT_FLAG,IEC_COMPLIANT_REM,IEC_ACT_STATUS,’||
‘IEC_USER_ID,IEC_DATE,IEC_DT_LAST_CHANGED) VALUES (”’||
IEC_CODE||”’,”’||
REPLACE(REPLACE(IEC_NAME1,””,”””),’/’,’//’)||”’,”’||
REPLACE(REPLACE(IEC_NAME2,””,”””),’/’,’//’)||”’,”’||
REPLACE(REPLACE(IEC_ADDRESS1,””,”””),’/’,’//’)||”’,”’||
REPLACE(REPLACE(IEC_ADDRESS2,””,”””),’/’,’//’)||”’,”’||
REPLACE(REPLACE(IEC_ADDRESS3,””,”””),’/’,’//’)||”’,”’||
NVL(IEC_OCC_FLAG,’N’)||”’,”’||
IEC_ID||”’,”’||
”||”’,”’||
IEC_BRN||”’,”’||
”||”’,”’||
‘LIN’||”’,”’||
NVL(IEC_NATIONALITY,’MU’)||”’,”’||
REPLACE(REPLACE(IEC_BUSINESS_LINE,””,”””),’/’,’//’)||”’,”’||
IEC_TEL||”’,”’||
IEC_MOBILE_NO||”’,”’||
IEC_EMAIL||”’,”’||
NVL(IEC_COMPLIANT_FLAG,’Y’)||”’,”’||
”||”’,”’||
NVL(IEC_ACT_STATUS,’Y’)||”’,”TAES”,SYSDATE,SYSDATE);’
FROM IMP_EXP_CONSIGS
WHERE TRUNC(SYSDATE) BETWEEN TRUNC(IEC_DT_FROM) AND TRUNC(IEC_DT_TO);

Reply ↓
Vaasu on April 28, 2021 at 8:03 am said:

One of My table column is CLOB and having more than 50000 lines and am getting “PLS-00172: string literal too long” error while running the generated block. Please suggest how we can handl

Reply ↓
- Dmitry on October 22, 2021 at 5:45 pm said:
  
  rewrite to concatination with chunks of apropriate size
  
  Reply ↓
Purushothama on September 5, 2021 at 11:02 am said:

how to run the same returned blob from java code

Reply ↓

DBAORA

oracle expert presents

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

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

Leave a Reply Cancel reply