Even in Oracle 9iR2, the problems are compounded. In the said example, comment out RAISE statements and execute them we get a different answer!

Hi Tom,

I feel that a waste of code everytime I call a procedure from the main procedure, having the need of testing the errorcode variable to check whether it was successful or not.

Example:

CREATE OR REPLACE PACKAGE manel_test AS

PROCEDURE main(id_i IN NUMBER,
errorcode_o OUT NUMBER,
output_o OUT VARCHAR2);
END manel_test;
/
CREATE OR REPLACE PACKAGE BODY manel_test AS

PROCEDURE do_something(id_i IN NUMBER,
errorcode_o OUT NUMBER,
output_o OUT VARCHAR2) IS
a NUMBER;
BEGIN
SELECT 1
INTO a
FROM dual
WHERE rownum = id_i;
--
errorcode_o := 0;
EXCEPTION
WHEN no_data_found THEN
errorcode_o := 1;
output_o := 'ID ' || id_i || ' wasn''t found';
END do_something;
--
PROCEDURE main(id_i IN NUMBER,
errorcode_o OUT NUMBER,
output_o OUT VARCHAR2) IS
exit_flow EXCEPTION;
BEGIN
do_something(id_i => id_i, errorcode_o => errorcode_o, output_o => output_o);
-- Check for error
IF errorcode_o = 1 THEN
RAISE exit_flow;
END IF;

/* some more code here*/
errorcode_o := 0;
EXCEPTION
WHEN exit_flow THEN
errorcode_o := 1;
END main;

END manel_test;
/

I thought about creating a global exception, which was only used in the main procedure, and all the other procedures called from the main procedure can RAISE that exception.

Example:

CREATE OR REPLACE PACKAGE manel_test_nocopy AS

PROCEDURE main(id_i IN NUMBER,
errorcode_o OUT NUMBER,
output_o OUT VARCHAR2);

END manel_test_nocopy;
/
CREATE OR REPLACE PACKAGE BODY manel_test_nocopy AS
-- Exceptions
exit_flow EXCEPTION;
--
PROCEDURE do_something(id_i IN NUMBER,
errorcode_o OUT NOCOPY NUMBER,
output_o OUT NOCOPY VARCHAR2) IS
a NUMBER;
BEGIN
SELECT 1
INTO a
FROM dual
WHERE rownum = id_i;
EXCEPTION
WHEN no_data_found THEN
errorcode_o := 1;
output_o := 'ID '||id_i||' wasn''t found';
RAISE exit_flow;
END do_something;
--
PROCEDURE main(id_i IN NUMBER,
errorcode_o OUT NUMBER,
output_o OUT VARCHAR2) IS
BEGIN
do_something(id_i => id_i, errorcode_o => errorcode_o, output_o => output_o);

/* some more code here*/
errorcode_o := 0;
EXCEPTION
WHEN exit_flow THEN
errorcode_o := 1;
END main;

END manel_test_nocopy;
/

For testing I used 1 and 2, where 1 completes successfully (errocode = 0) and 2 generates as exception ( errocode = 1 and output = 'ID 2 wasn't found').

My questions are:
1 - Do you see any side effect on using the NOCOPY hint in this particular situation?
2 - What are your thoughts about this coding practice?

Thanks in advance,
Manuel Vidigal

I was pretty sure you would say something like that. I was also fearing a "I Hate your code" sentence :).

The problem is that I cannot change the top-level client and I must output the errorcode and errormessage. That will not change.

Assuming that you really must output this two variables, what would be your approuch?

Thanks for yout help Tom.

Could you please provide me an example of how you would pass the error message (ex: "Service not found" or "Cliente not found") from the low level procedures to the main level one? I'm not understanding how if I raise the error on the low level ones, and not having an output variable on with that error, how can I in the main procedure know which of the queries failed.

CREATE OR REPLACE PACKAGE BODY example AS
--
PROCEDURE get_service(service_name_i VARCHAR2,
service_id_o NUMBER) IS
BEGIN
SELECT service_id
INTO service_id_o
FROM service;
WHERE service_name = service_name_i;

EXCEPTION
WHEN no_data_found THEN
RAISE;
END get_service;
--
PROCEDURE get_client(client_name_i VARCHAR2,
client_id_o NUMBER) IS
BEGIN
SELECT client_id
INTO client_id_o
FROM client
WHERE client_name = client_name_i;
EXCEPTION
WHEN no_data_found THEN
RAISE;
END get_client;
--
PROCEDURE main_wrapper(service_name_i IN VARCHAR2,
client_name_i IN VARCHAR2,
errorcode_o OUT NUMBER,
output_o OUT VARCHAR2) IS
-- Local Variables
l_service_id NUMBER;
l_client_id NUMBER;
-- Exception
exit_flow EXCEPTION;
BEGIN
-- Check Input Parameters
IF service_name_i IS NULL THEN
output_o := 'Service Name cannot be null';
RAISE exit_flow;
ELSIF client_name_i IS NULL THEN
output_o := 'Service Name cannot be null';
RAISE exit_flow;
END IF;
-- Get Service ID
get_service(service_name_i => service_name_i, service_id_o => l_service_id);
-- Get Client ID
get_client(client_name_i => client_name_i, client_id_o => l_client_id);
--
/* Create Service Equipments Logic */
--
errorcode_o := 0;
output_o := 'OK';
EXCEPTION
WHEN exit_flow THEN
errorcode_o := 1;
WHEN no_data_found THEN
errorcode_o := 1;
--output_o := 'HOW DO I POPULATE THIS VARIABLE? I WANT TO SAY WHICH OF THE QUERIES FAILED'
WHEN OTHERS THEN
errorcode_o := 1;
output_o := SQLERRM || ' - ' || dbms_utility.format_error_backtrace;
-- Log Error
END main_wrapper;
END example;


The process that calls main_wrapper, is a background process that has serveral request to process, and will process them one by one, writting in a output table if it was successful or not.

Thanks Tom.

As always, you are my most valuable source.