Home » SQL & PL/SQL » SQL & PL/SQL » Missing output parameters values after using RAISE
icon8.gif  Missing output parameters values after using RAISE [message #188233] Thu, 17 August 2006 08:47 Go to next message
eliseohg
Messages: 2
Registered: August 2006
Location: Caracas
Junior Member
Hi,

I've written a package who forces a WHEN OTHERS exception, once in the OTHERS exception, it sets two output parameters, one containing the error code and the other the error message (SQLCODE and SQLERRM).

After issuing the RAISE instruction, code returns to the caller program (in this case a PL-SQL block). the values of the output parameters came NULL.

Otherwise, i declared two global variables in the package specification (wg_cod_error and wg_desc_error) and set them in the exception area and after returning to the pl/sql block they have the expected values.

Is this a bug? what's wrong?

the code of the package is:

SPEC:

CREATE OR REPLACE PACKAGE Pk_Eahg AS
wg_cod_error NUMBER;
wg_desc_error VARCHAR2(300);
PROCEDURE proc_PruebaRaise
(o_monto OUT NUMBER,
o_cod_error OUT NUMBER,
o_desc_error OUT VARCHAR2);
END Pk_Eahg;
/

BODY:

CREATE OR REPLACE PACKAGE BODY Pk_Eahg AS
PROCEDURE proc_PruebaRaise
(o_monto OUT NUMBER,
o_cod_error OUT NUMBER,
o_desc_error OUT VARCHAR2)
IS
wk_id_arco NUMBER;
BEGIN

o_monto := 5414474;

-- THIS SELECT THROWS A TOO MANY ROWS EXCEPTION
SELECT 1
INTO wk_id_arco
FROM ANY_TABLE_WITH_MORE_THAN_ONE_ROW;

EXCEPTION
WHEN OTHERS THEN
o_cod_error := SQLCODE;
o_desc_error := SQLERRM;
wg_cod_error := o_cod_error;
wg_desc_error := o_desc_error;
DBMS_OUTPUT.PUT_LINE('EN EL PROCEDIMIENTO. EN LA EXCEPCION WHEN OTHERS O_COD_ERROR: ' || o_cod_error || ' O_DESC_ERROR: ' || o_desc_error);
-- IF YOU COMMENT BOTH LINES THE VALUES ARE RETURNED!!!!!! BECAUSE IT ASSUMES THERE IS NO ERROR.
-- RAISE;
RAISE_APPLICATION_ERROR(-20500, 'ERROR EN LA APLICACION: ' || o_cod_error || ' ' || o_desc_error);
END;
--Fin del package
END Pk_Eahg;
/

THE PL/SQL BLOCK:

DECLARE
mi_cod_error NUMBER;
mi_desc_error VARCHAR2(300);
mi_monto NUMBER;
error_aplicacion EXCEPTION;
BEGIN
Pk_Eahg.proc_PruebaRaise(mi_monto, mi_cod_error, mi_desc_error);
DBMS_OUTPUT.PUT_LINE('cod: '|| mi_cod_error || ' desc: ' || mi_desc_error);
DBMS_OUTPUT.PUT_LINE('Monto: ' || mi_monto);
IF NVL(mi_cod_error,0) <> 0 THEN
DBMS_OUTPUT.PUT_LINE('REGRESO DE LA RUTINA CON COD_ERROR = ' || mi_cod_error || ' DESC_ERROR = ' || mi_desc_error);
END IF;
EXCEPTION
WHEN error_aplicacion THEN
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SALE POR EL WHEN OTHERS MI_Cod_Error: ' || mi_cod_error || ' MI_Desc_Error: ' || mi_desc_error);
DBMS_OUTPUT.PUT_LINE('GLOBALES: WG_COD_ERROR: ' || Pk_Eahg.wg_cod_error || ' WG_DESC_ERROR: ' || Pk_Eahg.wg_desc_error);
DBMS_OUTPUT.PUT_LINE('Monto: ' || mi_monto);
RAISE_APPLICATION_ERROR(-20200,'ERROR EN LA EJECUCION DEL PROCEDIMIENTO');
END;

I Apologies, first for my english and second the example is in spanish.

Thanks,

Eliseo Hernandez
Caracas, Venezuela
Re: Missing output parameters values after using RAISE [message #188276 is a reply to message #188233] Thu, 17 August 2006 14:35 Go to previous messageGo to next message
annagel
Messages: 220
Registered: April 2006
Senior Member
Declare the params nocopy. I think that will fix your problem. When call the raise application error call it raises an exception and so the procedure returns with an exception in which case out params are not copied back to the calling procedure.

When you use nocopy in the declaration the params are not copied fresh for the sub procedure from the caller, so when their value is modified in the sub procedure, the caller values are modified at exactly the same time.

Andrew
icon14.gif  Re: Missing output parameters values after using RAISE [message #188281 is a reply to message #188276] Thu, 17 August 2006 15:25 Go to previous message
eliseohg
Messages: 2
Registered: August 2006
Location: Caracas
Junior Member
Thanks it works!!!.

I didn't knew that option.

The first step in the problem is solved. Now i have to check if the caller (a program written in "C") takes the error and handles the right way.

Thans again.

Eliseo.

Previous Topic: SQL/XML and LONG columns - getting ORA-00932
Next Topic: How to select Default value from column ?
Goto Forum:
  


Current Time: Thu Dec 08 22:00:17 CST 2016

Total time taken to generate the page: 0.15714 seconds