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 !!!!
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:

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

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.
slightwv (䄆 Netminder)Connect With a Mentor Commented:
I'll defer to the docs:

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?
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
You can use but they will not print anything...

try this :

dbms_output.put_line('sqlcode:' || sqlcode);
dbms_output.put_line('sql errm:' || sqlerrm);

output is :

sql errm:ORA-0000: normal, successful completion
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

d27m11yAuthor Commented:
Well, this was an interview question and I answered NO and I was not given any scenario..
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 :)

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.
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.