Home » SQL & PL/SQL » SQL & PL/SQL » Does the value is set to null when exception raised? (Oracle9.2.0.3)
Does the value is set to null when exception raised? [message #440036] Wed, 20 January 2010 13:52 Go to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
CREATE or replace PROCEDURE addtuple3( b OUT NUMBER)
    AS
    C NUMBER;
    BEGIN
        b := 4;
        SELECT C INTO E FROM G100;
    END;
/

Procedure created.

SQL>
 DECLARE
     v NUMBER;
  BEGIN
     addtuple3(v);
  END;
/

This will raise exception. But if exception is raised does the value of b is set to null after the exception occured or it will keep previous value 4 returned from the procedurewhen the program is called?
Re: Does the value is set to null when exception raised? [message #440038 is a reply to message #440036] Wed, 20 January 2010 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not raise any exception due to its execution as it does not compile:
SQL> CREATE or replace PROCEDURE addtuple3( b OUT NUMBER)
  2      AS
  3      C NUMBER;
  4      BEGIN
  5          b := 4;
  6          SELECT C INTO E FROM G100;
  7      END;
  8  /

Warning: Procedure created with compilation errors.

So the exception is the one that when you use any invalid code:
SQL> DECLARE
  2       v NUMBER;
  3    BEGIN
  4       addtuple3(v);
  5    END;
  6  /
     addtuple3(v);
     *
ERROR at line 4:
ORA-06550: line 4, column 6:
PLS-00905: object MICHEL.ADDTUPLE3 is invalid
ORA-06550: line 4, column 6:
PL/SQL: Statement ignored

So, as it does not execute the procedure, what could be the value?
And why don't you just try it?

Regards
Michel

[Updated on: Wed, 20 January 2010 13:59]

Report message to a moderator

Re: Does the value is set to null when exception raised? [message #440039 is a reply to message #440038] Wed, 20 January 2010 14:00 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sorry it should be E
SQL> CREATE or replace PROCEDURE addtuple3( b OUT NUMBER)
  2      AS
  3      E NUMBER;
  4      BEGIN
  5          b := 4;
             --the below sql is returning no rows
  6          SELECT C INTO E FROM G100;
  7      END;
  8  /


Re: Does the value is set to null when exception raised? [message #440040 is a reply to message #440039] Wed, 20 January 2010 14:02 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
My question is whenever an exception occured does the variable value set to null??

Re: Does the value is set to null when exception raised? [message #440041 is a reply to message #440039] Wed, 20 January 2010 14:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE or replace PROCEDURE addtuple3( b OUT NUMBER)
  2      AS
  3      C NUMBER;
  4      BEGIN
  5          b := 4;
  6          select 1 into c from dual where 1=0;
  7      END;
  8  /

Procedure created.

SQL> DECLARE
  2       v NUMBER;
  3    BEGIN
  4       addtuple3(v);
  5       dbms_output.put_line(v);
  6    END;
  7  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "MICHEL.ADDTUPLE3", line 6
ORA-06512: at line 4

There is no value, as there is an exception.
Now if you trap the exception, you can see:
SQL> DECLARE
  2       v NUMBER;
  3    BEGIN
  4       addtuple3(v);
  5       dbms_output.put_line(v);
  6    exception when no_data_found then dbms_output.put_line(v);
  7    END;
  8  /


PL/SQL procedure successfully completed.

NULL, well maybe it should be clearer if v is initialized:
SQL> DECLARE
  2       v NUMBER := 0;
  3    BEGIN
  4       addtuple3(v);
  5       dbms_output.put_line(v);
  6    exception when no_data_found then dbms_output.put_line(v);
  7    END;
  8  /
0

PL/SQL procedure successfully completed.

Conclusion?

Regards
Michel

[Updated on: Wed, 20 January 2010 14:05]

Report message to a moderator

Re: Does the value is set to null when exception raised? [message #440042 is a reply to message #440041] Wed, 20 January 2010 14:07 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Quote:

There is no value, as there is an exception.

I am not trapping the exception. I just want to know if the value is set to null



Re: Does the value is set to null when exception raised? [message #440043 is a reply to message #440041] Wed, 20 January 2010 14:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now let's confirm by the documentation.
Database PL/SQL User's Guide and Reference
Chapter 10 Handling PL/SQL Errors
Section Handling Raised PL/SQL Exceptions
Paragraph Catching Unhandled Exceptions

Quote:
Unhandled exceptions can also affect subprograms. If you exit a subprogram successfully, PL/SQL assigns values to OUT parameters. However, if you exit with an unhandled exception, PL/SQL does not assign values to OUT parameters (unless they are NOCOPY parameters).


Regards
Michel
Re: Does the value is set to null when exception raised? [message #440044 is a reply to message #440042] Wed, 20 January 2010 14:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am not trapping the exception. I just want to know if the value is set to null

If in your example you do not trap the exception, as I showed it, it does not matter what the value is as you get out of the PL/SQL block without being able to use the variable.

Regards
Michel

[Updated on: Wed, 20 January 2010 14:12]

Report message to a moderator

Re: Does the value is set to null when exception raised? [message #440045 is a reply to message #440043] Wed, 20 January 2010 14:12 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Quote:

However, if you exit with an unhandled exception, PL/SQL does not assign values to OUT parameters (unless they are NOCOPY parameters).



That means if we dont use nocopy then value is set to null for exception caused??

[Updated on: Wed, 20 January 2010 14:12]

Report message to a moderator

Re: Does the value is set to null when exception raised? [message #440046 is a reply to message #440045] Wed, 20 January 2010 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
That means if we dont use nocopy then value is set to null for exception caused??

I think I showed you how to test it by yourself.

Regards
Michel

[Updated on: Wed, 20 January 2010 14:15]

Report message to a moderator

Re: Does the value is set to null when exception raised? [message #440047 is a reply to message #440046] Wed, 20 January 2010 14:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> CREATE or replace PROCEDURE addtuple3( b OUT NOCOPY NUMBER)
  2      AS
  3      C NUMBER;
  4      BEGIN
  5          b := 4;
  6          select 1 into c from dual where 1=0;
  7      END;
  8  /

Procedure created.

SQL> DECLARE
  2       v NUMBER := 0;
  3    BEGIN
  4       addtuple3(v);
  5       dbms_output.put_line(v);
  6    exception when no_data_found then dbms_output.put_line(v);
  7    END;
  8  /
4

PL/SQL procedure successfully completed.
Re: Does the value is set to null when exception raised? [message #440048 is a reply to message #440046] Wed, 20 January 2010 14:16 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
exception is raised... we will obviously can not see the value as we will get the error. My question was in that case does it set the value to null internally?? I am not handling th exception. I think that it would set the value to null but finding now way to prove it.

[Updated on: Wed, 20 January 2010 14:17]

Report message to a moderator

Re: Does the value is set to null when exception raised? [message #440049 is a reply to message #440048] Wed, 20 January 2010 14:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My question was in that case does it set the value to null??

Why do you care if you can never see it?
It can null, it can be 0, it can one zillion, you can never see it.

Regards
Michel
Re: Does the value is set to null when exception raised? [message #440050 is a reply to message #440049] Wed, 20 January 2010 14:19 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
It should be NULL....but finding no way to prove it.
Re: Does the value is set to null when exception raised? [message #440051 is a reply to message #440050] Wed, 20 January 2010 14:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You are wrong and the way to show it is to trap the exception in the caller block because the called one does not know if its caller traps the exception or not and so can't change its behaviour depending on this.
Now I showed you above what is the answer.

Regards
Michel

[Edit: typos]

[Updated on: Thu, 21 January 2010 01:22]

Report message to a moderator

Re: Does the value is set to null when exception raised? [message #440052 is a reply to message #440051] Wed, 20 January 2010 14:34 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
I know how to trap the exception. As I said, I am not trapping the exception. "IF EXCEPTION OCCURS WHAT WILL BE THE VALUE? I THINK ITS NULL ONLY"
Re: Does the value is set to null when exception raised? [message #440059 is a reply to message #440036] Wed, 20 January 2010 15:24 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
What Michel is trying to tell you is that if you don't catch the exception at some point then the entire program is going to crash and burn rendering any parameters (and variables) meaningless.

They won't be null or any other value they'll be out of scope.

You can't check the value of anything if it's crashed. So you need to catch the exception.

Michel's given you everything you need to check this yourself, plus a reference to the docs that tells you what happens.

Re: Does the value is set to null when exception raised? [message #440060 is a reply to message #440059] Wed, 20 January 2010 16:21 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What you ask is basically equivalent to "will the value be NULL if the computer is turned off?"

It makes no sense to wonder what the value might be after the exception is raised, the same way that it makes no sense to ask what the value might be when the computer is turned off.
Re: Does the value is set to null when exception raised? [message #440092 is a reply to message #440052] Thu, 21 January 2010 00:52 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
prachij593 wrote on Wed, 20 January 2010 21:34
I know how to trap the exception. As I said, I am not trapping the exception. "IF EXCEPTION OCCURS WHAT WILL BE THE VALUE? I THINK ITS NULL ONLY"

You just don't get what Michel already has shown you: trapping the exception has NOTHING to do with it, since the exception is caught in the calling module.
Apart from what everybody already told you (that is: if you do not catch the exception, the value of the local variable is of no interest, since it is unreachable), Michel's examples make it quite clear that the local variable holds its original value. (so not NULL, as you assume, just by making up functionality)
Previous Topic: Ping an IP from oracle-check for response.
Next Topic: Referering Long value in triger
Goto Forum:
  


Current Time: Mon Dec 05 08:47:34 CST 2016

Total time taken to generate the page: 0.05140 seconds