Solved

SQLCODE outside exception block

Posted on 2014-01-15
6
533 Views
Last Modified: 2014-01-19
Somebody asked me a question if  I can use SQLCODE and SQLERRM outside exception block, I am not sure. Please suggest !!!!
0
Comment
Question by:d27m11y
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 166 total points
ID: 39784237
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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 167 total points
ID: 39784256
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
 

Author Comment

by:d27m11y
ID: 39784288
Well, this was an interview question and I answered NO and I was not given any scenario..
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39784290
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39784292
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
 
LVL 35

Accepted Solution

by:
johnsone earned 167 total points
ID: 39785059
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Alternative to GTT for a temp table for further reuse in Oracle 8 60
join actual table rows based on the column 25 43
oracle query 3 34
Oracle perfomance issue. 4 48
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question