procedimientos - El procedimiento de Oracle solo funciona cuando se establece la salida del servidor;

procedimientos y funciones oracle pl/sql

Question

Extraño comportamiento al intentar insertar un registro con un procedimiento almacenado. Solo funciona con set serveroutput on :

Gracias.

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

Answer 1

El error proviene de la creación de instancias del paquete :

Cuando una sesión hace referencia a un elemento del paquete, Oracle Database crea una instancia del paquete para esa sesión. Cada sesión que hace referencia a un paquete tiene su propia instanciación de ese paquete.

Cuando Oracle Database crea una instancia de un paquete, lo inicializa. La inicialización incluye cualquiera de los siguientes casos: ...

• Ejecutando la parte de inicialización del cuerpo del paquete

Esa instalación solo ocurre una vez por sesión. Su primera llamada al procedimiento ejecuta la sección de inicialización. La segunda llamada no. El set serveroutput on no está teniendo ningún efecto, es solo que es la segunda llamada que funciona, y eso es porque esa inicialización no se llama la segunda vez.

Por lo tanto, el error proviene de la parte de inicialización del cuerpo del paquete, que está justo al final: después de todos los procedimientos y funciones, etc., debe begin antes del final del paquete. Algo como:

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

El código en ese bloque incluirá la línea 1743, y esa será una select ... into cual se encuentra más de una fila. No has mostrado ese código, pero es eso lo que debes investigar.