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

概述尝试使用存储过程插入记录的奇怪行为. 仅适用于set serveroutput： 谢谢. SQL*Plus: Release 11.2.0.1.0 Production on Tue May 31 22:48:25 2016Copyright (c) 1982, 2010, Oracle. All rights reserved.Connected to:Oracle Databas 尝试使用存储过程插入记录的奇怪行为.
仅适用于set serveroutput：

谢谢.

sql*Plus: Release 11.2.0.1.0 Production on Tue May 31 22:48:25 2016copyright (c) 1982,2010,Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning,Real Application Clusters,automatic Storage Management,olAP,Data Mining and Real Application Testing optionssql> 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 rowsORA-06512: at "TESTUSR.abc_utils",line 1743ORA-06512: at line 1sql> 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 1743end abc_utils;/

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

总结

以上是内存溢出为你收集整理的Oracle过程仅在设置serveroutput on时有效;全部内容，希望文章能够帮你解决Oracle过程仅在设置serveroutput on时有效;所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。