Oracle过程仅在设置serveroutput on时有效;

尝试使用存储过程插入记录的奇怪行为.

仅适用于set serveroutput：

谢谢.

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 31 22:48:25 2016

Copyright (c) 1982,2010,Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning,Real Application Clusters,Automatic Storage Management,OLAP,Data Mining and Real Application Testing options

SQL> exec abc_utils.abc_init(p_table_name => 'ABC_TEST',p_batch_alias => 'TST');
BEGIN abc_utils.abc_init(p_table_name => 'ABC_TEST',p_batch_alias => 'TST'); END;

*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "TESTUSR.abc_utils",line 1743
ORA-06512: at line 1


SQL> set serveroutput on;
SQL> exec abc_utils.abc_init(p_table_name => 'ABC_TEST',p_batch_alias => 'TST');

PL/SQL procedure successfully completed.


    PROCEDURE abc_init(p_table_name    IN VARCHAR2,p_batch_alias   IN VARCHAR2) IS

    v_sql                VARCHAR2(32000);

    --object caller variables
    v_owner              VARCHAR2(30 CHAR);
    v_obj_name           VARCHAR2(30 CHAR);
    v_line_no            NUMBER;
    v_caller_type        VARCHAR2(30 CHAR);
    v_caller             VARCHAR2(4000 CHAR);


    BEGIN
     owa_util.who_called_me(v_owner,v_obj_name,v_line_no,v_caller_type);
       v_caller := 'Object Name: ' || $$plsql_unit || '; Caller Name: ' || v_obj_name || '; Caller Line: ' || to_char(v_line_no);

         --initialise summary table
       v_sql := 'INSERT INTO INIT_SUMMARY (BATCH_ALIAS,TABLE_NAME,START_DT,BATCH_USER,BATCH_RUN) ' ||
                'SELECT a.BATCH_NAME,:1,:2,a.BATCH_USER,:3 FROM INIT_CFG a WHERE 1 = 1 AND BATCH_NAME = :4';

       EXECUTE IMMEDIATE v_sql USING p_table_name,SYSDATE,summary_seq.nextval,p_batch_alias;
       COMMIT;

       EXCEPTION
          WHEN OTHERS THEN
             gv_err_msg := substr('Back Trace: ',1,4000);
             RAISE_APPLICATION_ERROR(-20001,'abc_init: ' || gv_err_msg);
    END abc_init;
    --********************************************************************

解决方法

错误来自
package instantiation：

When a session references a package item,Oracle Database instantiates the package for that session. Every session that references a package has its own instantiation of that package.

When Oracle Database instantiates a package,it initializes it. Initialization includes whichever of the following are applicable:
…

• Executing the initialization part of the package body

这种情况只发生在每次会议一次.您对该过程的第一次调用将执行初始化部分.第二个电话没有. set serveroutput on没有任何影响,只是它是第二个调用,这是因为初始化不是第二次调用.

因此,错误来自包体的初始化部分,最后是正确的 – 在所有程序和功能等之后,您在包的最终结束之前开始了.就像是：

create or replace package body abc_utils as
  ...
  some_var number; -- package state variable
  ...
  procedure abc_init is
    ...
  begin
    ...
  end abc_init;
  ...
/* package initialisation */
begin
  select some_col into some_var from some_table;  -- line 1743
end abc_utils;
/

该块中的代码将包括第1743行,这将是一个select …,其中找到多行.您尚未显示该代码,但这是您需要调查的代码.