仅当将serveroutput设置为on时，Oracle过程才有效. [英] Oracle procedure works only when set serveroutput on;

问题描述

尝试插入具有存储过程的记录的奇怪行为. 仅适用于set serveroutput on:

Strange behavior trying to insert a record with a stored procedure. Only works with set serveroutput on:

谢谢.

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

推荐答案

错误来自

当会话引用一个包项目时，Oracle数据库将实例化该会话的包.引用程序包的每个会话都有其自己的该程序包的实例.

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.

Oracle数据库实例化程序包时，将对其进行初始化.初始化包括以下任意一项: ...

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

• 执行程序包主体的初始化部分

该状态仅在每个会话中发生一次.您对过程的第一次调用将执行初始化部分.第二个电话没有. set serveroutput on没有任何作用，只是它是第二个调用起作用，这是因为初始化没有被第二次调用.

That instatiation only happens once per session. Your first call to the procedure executes the initialisation section. The second call does not. The set serveroutput on isn't having any effect, it is just that it is the second call that works, and that is because that initialisation isn't called the second time.

因此错误来自封装体的初始化部分，该部分恰好在最后-在所有过程和功能等之后，在封装体的最后一个end之前有一个begin.像这样:

So the error is coming from the initialisation part of the package body, which is right at the end - after all the procedures and functions etc. you have a begin before the final end of the package. Something like:

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 ... into，它将查找多个行.您尚未显示该代码，但您需要研究它.

The code in that block will include line 1743, and that will be a select ... into which is finding more than one row. You haven't shown that code, but it is that which you need to investigate.

这篇关于仅当将serveroutput设置为on时，Oracle过程才有效.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！