SQLCODE outside exception block

Somebody asked me a question if  I can use SQLCODE and SQLERRM outside exception block, I am not sure. Please suggest !!!!
d27m11yAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Think about it for a minute.  SQLCODE returns the error code from the previous statement.  If the previous statement had an error in it, then you would jump to an exception handler and never make it to the SQLCODE call.

Take for example this pseudo code:

begin
  insert into tab values (1, 37);
  if SQLCODE < 0 then
       dbms_output.put_line('In if statement');
  end if;
exception
   when dup_val_on_index then
       dbms_output.put_line('In error handler');
end;

Open in new window


Now, if that insert statement failed because you were violating a primary key, which message would get printed?

There may be a few cases of warnings (not errors) where processing would continue and not drop into an exception handler.  I cannot think of any, but that would be the only case where SQLCODE would contain a value other than 0 outside of an exception handler.
0
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I'll defer to the docs:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/sqlcode_function.htm#LNPLS01349

Pay attention to:  In an exception handler
SQLCODE Function

In an exception handler, the SQLCODE function returns the numeric code of the exception being handled. (Outside an exception handler, SQLCODE returns 0.)

To add to the docs:  I've never seen it used outside of an exception handler.

Can I ask what the person that asked was thinking where it might be useful besides during an exception?
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
You can use but they will not print anything...

try this :

begin
dbms_output.put_line('checking...');
dbms_output.put_line('sqlcode:' || sqlcode);
dbms_output.put_line('sql errm:' || sqlerrm);
end;
/

output is :

checking...
sqlcode:0
sql errm:ORA-0000: normal, successful completion
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
d27m11yAuthor Commented:
Well, this was an interview question and I answered NO and I was not given any scenario..
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
yep. that is just to test how good one is in understand what can be done and what cannot be done etc with the techinical stuff :)

Thanks,
0
 
slightwv (䄆 Netminder) Commented:
Looks like you 'can' but the answer is 'it doesn't do anything'.

Unless, the interviewers know something we don't.  Which is always possible!

Leave this open for a while to see if anyone else knows of a way.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.