【问题描述】

执行下面的存储过程，会报错ORA-00913: too many values。

1.创建测试表

create table t99 as select 'alter system disconnect session '''||wc.sid||','||wc.serial#||''' immediate;' as com from v$session wc where 1=2;

2.创建存储过程

create or replace procedure tab1
Authid Current_User 
as  
begin  
execute immediate 'insert into ztmon.t99 select ''alter system disconnect session ''''||wc.sid||'',''||wc.serial#||'''' immediate;'' as com from v$session wc';
end;
/

3.执行此存储过程报错。

execute tab1;
ORA-00913: too many values
ORA-06512: at "SYS.TAB1", line 5

【解决过程】

ORA-00913: too many values，这个问题一般都是由于insert语句插入值和表不一致导致。对于存储过程我们可以这样来观察问题。

1.修改一下存储过程，采用v_sql变量的形式，使结构更加清晰

create or replace procedure tab1
Authid Current_User 
as  
v_sql   VARCHAR2(2000);
begin  
v_sql:='insert into t99 select ''alter system disconnect session ''''||wc.sid||'',''||wc.serial#||'''' immediate;'' as com from v$session wc';
execute immediate v_sql;
end;
/

2.在执行语句之前，先显示一下语句文本，增加dbms_output.put_line(v_sql);

create or replace procedure tab1
Authid Current_User 
as  
v_sql   VARCHAR2(2000);
begin  
v_sql:='insert into t99 select ''alter system disconnect session ''''||wc.sid||'',''||wc.serial#||'''' immediate;'' as com from v$session wc';
dbms_output.put_line(v_sql);
execute immediate v_sql;
end;
/

此次执行时结果我们可以看到执行的sql，在这个sql里面'alter system disconnect session
''应该是'''，因此语句肯定是不对的。

SQL> exec tab1
insert into t99(com) select 'alter system disconnect session
''||wc.sid||','||wc.serial#||'' immediate;' as com from v$session wc
BEGIN tab1; END;

*
ERROR at line 1:
ORA-00913: too many values
ORA-06512: at "SYS.TAB1", line 8
ORA-06512: at line 1

这样我们就得知了最终的原因，是由于单引号写的数量不对。

【最终解决】

SQL> create or replace procedure tab1
  2  Authid Current_User 
  3  as  
  4  v_sql   VARCHAR2(2000);
  5  begin  
v_sql:='insert into t99(com) select ''alter system disconnect session ''''''||wc.sid||'',''||wc.serial#||'''''' immediate;'' as com from v$session wc';
  7  dbms_output.put_line(v_sql);
  8  execute immediate v_sql;
  9  end;
 10  /

Procedure created.

SQL> show errors
No errors.
SQL> exec tab1
insert into t99(com) select 'alter system disconnect session
'''||wc.sid||','||wc.serial#||''' immediate;' as com from v$session wc

PL/SQL procedure successfully completed.

SQL>