立即执行ORA-00900

Question

我正在尝试从现有表中创建insert语句。然而，我最终面对的是ORA-00900问题。令人惊讶的是，当我使用execute immediate独立执行结果时，它工作得很好。

让我们创建两个表：

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;

下面是我正在处理的获取数据的块。此块将使用SQL。命令行参数将是：

SQL> @export.ddl my_schema a,b

现在，我不在这里编写SET语句。

/* 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;

在这个代码中，我要面对：

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

它是执行立即语句的行号。

但是，如果我只复制dbms_output (注释掉执行立即语句和下面的循环)，即：

'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'

并把它当作：

/* 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;

然后得到所需的输出，即：

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

我的问题是:为什么在第一个代码(export.ddl)中执行立即给我错误？我能做些什么来阻止它，并从第二步(second.ddl)中拯救自己？

这意味着，使用一个脚本在insert.ddl脚本中获取insert语句。

预先多谢:-)

Answer 1

你输入了太多(转义的)引语。从dbms_output中看到的生成语句是

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

当您在第二个块中运行它时，当您将它赋值给statemement变量时，您将它作为字符串文本来处理--而该赋值有效地解除了所有转义引号。当您在第一个块中直接运行它时，开始和结束引号是无效的。换句话说，复制输出(包括这些引号)并将其直接粘贴到SQL*Plus作为SQL语句-然后您将看到ORA-00900。

仅仅删除最外层的是不够的；只要删除了这些，i.er。改变

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

至

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

..。它得到"ORA-00923: FROM关键字未在预期的位置找到“。要消除这个问题，您需要从insert_smt中删除多余的引号，并且在stmt之前关闭。

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

和

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

..。它现在将运行，但不会产生所需的输出：

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)||')

你也需要删除很多其他转义引号。

Answer 2

如果您只想生成insert语句(并不完全是这样)，那么在SQLDeveloper中有一个函数，它生成insert语句。您只需离开-点击table>export，并取消检查“导出DDL”。Next>下一步，您有纯正的SQL语句。