Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Jon.

Asked: June 03, 2003 - 10:25 am UTC

Last updated: May 28, 2010 - 11:03 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Can you explain the different behavior of NOCOPY in the following two cases? The only difference seems to be putting a precision on the variable "a" in the second case, and yet they appear to function quite differently.

SQL> DECLARE
2 a NUMBER;
3 b NUMBER;
4 PROCEDURE dummyProcedure (x IN OUT NOCOPY NUMBER) IS
5 e EXCEPTION;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(x);
8 x := 2;
9 RAISE e;
10 EXCEPTION
11 WHEN e THEN
12 x := 1;
13 RAISE;
14 END;
15 BEGIN
16 a := 0;
17 dummyProcedure(a);
18 EXCEPTION
19 WHEN OTHERS THEN
20 DBMS_OUTPUT.PUT_LINE('A :'||a);
21 END;
22 /
0
A :1

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
2 a NUMBER(4);
3 b NUMBER(4);
4 PROCEDURE dummyProcedure (x IN OUT NOCOPY NUMBER) IS
5 e EXCEPTION;
6 BEGIN
7 DBMS_OUTPUT.PUT_LINE(x);
8 x := 2;
9 RAISE e;
10 EXCEPTION
11 WHEN e THEN
12 x := 1;
13 RAISE;
14 END;
15 BEGIN
16 a := 0;
17 dummyProcedure(a);
18 EXCEPTION
19 WHEN OTHERS THEN
20 DBMS_OUTPUT.PUT_LINE('A :'||a);
21 END;
22 /
0
A :0

PL/SQL procedure successfully completed.

and we said...

NOCOPY is a hint.

When the types did not match as it wanted -- the hint was IGNORED. (as it is documented that it might be)

The second example was using "copy semantics" and behaved exactly as a procedure would behave without NOCOPY.


The first one shows the insidous side effect of NOCOPY - that the out parameter values may be modified -- even in the event of an ERROR.




Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

More anomalies

Dinesh, July 16, 2007 - 2:37 pm UTC

Even in Oracle 9iR2, the problems are compounded. In the said example, comment out RAISE statements and execute them we get a different answer!
Tom Kyte
July 17, 2007 - 11:36 am UTC

eh???

you should be aware that when you use nocopy, the parameter values may be affected by side effects and their values are UNPREDICABLE in the event of an error. This is by DESIGN. It is exactly what you have to expect, that is why it is not the default manner of execution, but a choice you have to make yourself to do (or not)

NOCOPY and Exceptions

Manuel Vidigal, May 27, 2010 - 9:30 am UTC

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
Tom Kyte
May 27, 2010 - 10:39 am UTC

why do you want to catch exceptions at low levels.

I would (I do, I encourage, I would reject code that doesn't) let the detailed exception propagate up to the uppermost level. A global exception - what exactly does that buy you???? besides lost information???


The only exceptions lower level code should catch are exceptions that are not exceptions - eg:

begin
....
begin
select x into y from t where ;
exception
when no_data_found then y := 42;
end;
...
end;



It should catch NOTHING ELSE (it cannot deal with it). The top level calling bit of code (typically - this should be the CLIENT) will get any other exceptions (which are truly exceptions) and do something with them (eg: log them, translate them into something the end user can live with, and display them).


If the top level client is plsql (think APEX) then it can catch it (and do logging, translation and display).


If the top level client is not plsql (think Java, VB, whatever), then plsql at the TOP LEVEL could still catch it (to log it in a table with an autonomous transaction) but then MUST RE-RAISE IT...



Manuel Vidigal, May 28, 2010 - 4:32 am UTC

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?
Tom Kyte
May 28, 2010 - 8:05 am UTC

then write wrapper routines that pretend they are the top level client - do it right at all other levels (use exceptions as they are intended to be used) and only do this silly return code thing (that is SO 1980's) at this interface layer.

Manuel Vidigal, May 28, 2010 - 10:59 am UTC

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.
Tom Kyte
May 28, 2010 - 11:03 am UTC

raise_application_error( -<some error code>, 'your error message' );


eg:

raise_application_error( -20001, 'Service Not Found' );


you will get a sqlcode= ora-20001, sqlerrm = 'Service Not Found'

Manuel Vidigal, May 28, 2010 - 11:46 am UTC

Thanks Tom.

As always, you are my most valuable source.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library