execute immediate ORA-00900

Question

I am trying to create insert statements from existing table. However, I end up facing ORA-00900 problem. Surprisingly when I execute the results independently using execute immediate then it works fine.

Let's create 2 tables:

create table a (
 name varchar2 (100),
 age integer
);
insert into a values ('Tom', 1);
commit;
insert into a values ('John', 2);
commit;

create table b (
 name varchar2 (100),
 age integer
);
insert into b values ('Jane', 1);
commit;
insert into b values ('Eric', 2);
commit;

Now here is the block which I am working on to get the data. This block will be used using SQL Plus. The command line arguments will be:

SQL> @export.ddl my_schema a,b

For now, I am not writing SET statements here.

/* export.ddl */
declare 
  p_schema_name varchar2(1000) := upper('&1');
  p_table_names varchar2(4000) := upper('&2');
  l_statement varchar2(30000);
  type tmp_table is table of varchar2(30000);
  l_result tmp_table;  

begin
  for c_table in (    
    select 
      regexp_substr (replace(p_table_names, ' ', ''), '[^,]+', 1, level) as t_name
    from dual
    connect by 
      regexp_substr (replace(p_table_names, ' ', ''), '[^,]+', 1, level) is not null)
  loop
    for c_col in (
      select 
        '''select '''||ins.insert_stmt||'('||recs.select_rows||''')'''' stmt from '||recs.owner||'.'||recs.table_name||'''' as statement
      from (  
        select 
          atc.owner, 
          atc.table_name, 
          ''||listagg(case when atc.data_type = 'VARCHAR2' then '''''||decode('||atc.column_name||', null, ''''null'''', ''''''''''''''''||'||atc.column_name||'||'''''''''''''''')||''' 
                           when atc.data_type = 'NUMBER' then '''''||decode('||atc.column_name||', null, ''''null'''', '||atc.column_name||')||'''  
                           when atc.data_type = 'DATE' then 'to_date(''''''||to_char('||atc.column_name||', ''DD-MON-YYYY HH24:MI:SS'')||'''''', ''''DD-MON-YYYY HH24:MI:SS'''')'  
                      end, ''', ') within group (order by atc.column_id) as select_rows 
        from dba_tab_columns atc
        where atc.table_name in (c_table.t_name) -- here
        group by atc.owner, atc.table_name) recs,  
      (  
        select 
          atc.owner, 
          atc.table_name, 
          '''insert into '||atc.owner||'.'||atc.table_name||' ('||listagg(atc.column_name, ', ') within group (order by atc.column_id)||') VALUES ' as insert_stmt  
         from all_tab_columns atc
         where atc.table_name in (c_table.t_name) -- here
         group by atc.owner, atc.table_name  
      ) ins  
      where recs.owner = ins.owner  
      and recs.table_name = ins.table_name)
    loop
      l_statement := c_col.statement;
      dbms_output.put_line(l_statement);
        execute immediate l_statement bulk collect into l_result;
        for i in 1 .. l_result.count() loop
          dbms_output.put_line(l_result(i));
        end loop;
    end loop;
  end loop;
end;

In this code I face:

ORA-00900: invalid SQL statement
ORA-06512: at line 44

which is the line number for execute immediate statement.

However, if I copy the dbms_output only (commenting out execute immediate statement and following loop) which is:

'select ''insert into MY_SCHEMA.A (NAME, AGE) VALUES (''||decode(NAME, null, ''null'', ''''''''||NAME||'''''''')||'', ''||decode(AGE, null, ''null'', AGE)||'')'' stmt from MY_SCHEMA.A'
'select ''insert into MY_SCHEMA.B (NAME, AGE) VALUES (''||decode(NAME, null, ''null'', ''''''''||NAME||'''''''')||'', ''||decode(AGE, null, ''null'', AGE)||'')'' stmt from MY_SCHEMA.B'

And use it like:

/* second.ddl */
declare 
  l_statememt varchar2(30000);
  type typ_varcharlist IS TABLE OF VARCHAR2(30000);
  col_list typ_varcharlist;  
  statemement varchar2(30000) := 'select ''insert into MY_SCHEMA.A (NAME, AGE) VALUES (''||decode(NAME, null, ''null'', ''''''''||NAME||'''''''')||'', ''||decode(AGE, null, ''null'', AGE)||'')'' stmt from MY_SCHEMA.A';
begin
  execute immediate statemement bulk collect into col_list;
  for i in 1 .. col_list.count() loop
      dbms_output.put_line(col_list(i));
  end loop;
end;

Then I get the required output which is:

insert into MY_SCHEMA.A (NAME, AGE) VALUES ('Tom', 1)
insert into MY_SCHEMA.A (NAME, AGE) VALUES ('John', 2)

My question is: Why execute immediate give me error in the first code (export.ddl)? What can I do to prevent it, and saving myself from the second step (second.ddl)?

That means, using one script to get insert statements in let's say insert.ddl script.

Thanks in advance :-)

Alex Poole · Accepted Answer

You're putting in too many (escaped) quotes. The generated statement you see from dbms_output is

'select ''insert into MY_SCHEMA.A (NAME, AGE) VALUES (''||decode(NAME, null, ''null'', ''''''''||NAME||'''''''')||'', ''||decode(AGE, null, ''null'', AGE)||'')'' stmt from MY_SCHEMA.A'

When you run that in your second block you're treating it as a string literal, when you assign it to the statemement variable - and that assignment unescapes all the escaped quotes, effectively. When you run it directly in the first block the opening and closing quotes aren't valid. Or to put it another way, copy that output (including those quotes) and paste it directly into SQL*Plus as a SQL statement - then you will see the ORA-00900.

Just removing the outermost ones isn't enough; with just those removed, i.er. changing

    '''select '''||ins.insert_stmt||'('||recs.select_rows||''')'''' stmt from '||recs.owner||'.'||recs.table_name||'''' as statement

to

    'select '''||ins.insert_stmt||'('||recs.select_rows||''')'' stmt from '||recs.owner||'.'||recs.table_name||'' as statement

... it gets "ORA-00923: FROM keyword not found where expected". To get rid of that you need to remove superfluous quotes from insert_smt and when that closes before stmt:

    'select '''||ins.insert_stmt||'('||recs.select_rows||''')'' stmt from '||recs.owner||'.'||recs.table_name||'' as statement

and

      'insert into '||atc.owner||'.'||atc.table_name||' ('||listagg(atc.column_name, ', ') within group (order by atc.column_id)||') VALUES ' as insert_stmt

... which will now run, but doesn't produce the output you want:

PL/SQL procedure successfully completed.

select 'insert into MYSCHEMA.A (NAME, AGE) VALUES (''||decode(NAME, null, ''null'', ''''''''||NAME||'''''''')||'', ''||decode(AGE, null, ''null'', AGE)||'')' stmt from MYSCHEMA.A
insert into MYSCHEMA.A (NAME, AGE) VALUES ('||decode(NAME, null, 'null', ''''||NAME||'''')||', '||decode(AGE, null, 'null', AGE)||')
insert into MYSCHEMA.A (NAME, AGE) VALUES ('||decode(NAME, null, 'null', ''''||NAME||'''')||', '||decode(AGE, null, 'null', AGE)||')
select 'insert into MYSCHEMA.B (NAME, AGE) VALUES (''||decode(NAME, null, ''null'', ''''''''||NAME||'''''''')||'', ''||decode(AGE, null, ''null'', AGE)||'')' stmt from STACKOVERFLOW.B
insert into MYSCHEMA.B (NAME, AGE) VALUES ('||decode(NAME, null, 'null', ''''||NAME||'''')||', '||decode(AGE, null, 'null', AGE)||')
insert into MYSCHEMA.B (NAME, AGE) VALUES ('||decode(NAME, null, 'null', ''''||NAME||'''')||', '||decode(AGE, null, 'null', AGE)||')

You need to remove a lot of the other escaped quotes too.

execute immediate ORA-00900

Answers (2)

Related Questions