[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQLERRM & SQLCODE

Posted on 2009-12-17
10
Medium Priority
?
2,174 Views
Last Modified: 2013-12-18
Hi Experts,

In the below which one is standard or correct format of SQLCODE and SQLERRM? Imagine my EMP table has 10 records. My doubt(concern)  is SQLERRM & SQLERRM(SQLCODE) returns the same message. Which one is standard?

DECLARE
       v_error NUMBER;
    BEGIN
       SELECT first_name
       INTO v_error
       FROM emp;
    EXCEPTION
       WHEN OTHERS
       THEN
         DBMS_OUTPUT.PUT_LINE('SQLERRM: '||SQLERRM);
         DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);
         DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLERRM(SQLCODE));
   END;
 /

Output::::

SQLERRM: ORA-01422: exact fetch returns more than requested number of rows
SQLCODE: -1422
SQLCODE: ORA-01422: exact fetch returns more than requested number of rows
0
Comment
Question by:victory_in
  • 5
  • 3
  • 2
10 Comments
 
LVL 21

Accepted Solution

by:
flow01 earned 1400 total points
ID: 26075396
SQLERRM  is enough  to inform about the last message.
SQLERRM is a function that returns for an sqlcode the description of the corresponding message in the current language.
If you don't provide a code to the function the argument defaults to the last sqlcode:
run the following example:
begin
  dbms_output.put_line('sqlerrm:' || sqlerrm);
  dbms_output.put_line('sqlcode:' || sqlcode);
  dbms_output.put_line('sqlerrm(0):' || sqlerrm(0));
  dbms_output.put_line('sqlerrm(-1):' || sqlerrm(-1));
  dbms_output.put_line('sqlerrm(-100):' || sqlerrm(-100));
  dbms_output.put_line('sqlerrm(100):' || sqlerrm(100));
  dbms_output.put_line('sqlerrm(-1403):' || sqlerrm(-1403));
  raise no_data_found;
exception when others then
  dbms_output.put_line('sqlcode:' || sqlcode);
  dbms_output.put_line('exception handling');
  dbms_output.put_line('sqlcode:' || sqlcode);
  dbms_output.put_line('sqlerrm:' || sqlerrm);
  dbms_output.put_line('sqlerrm(0):' || sqlerrm(0));
  dbms_output.put_line('sqlerrm(-1):' || sqlerrm(-1));
  dbms_output.put_line('sqlerrm(-100):' || sqlerrm(-100));
  dbms_output.put_line('sqlerrm(100):' || sqlerrm(100));
  dbms_output.put_line('sqlerrm(-1403):' || sqlerrm(-1403));

end;
/
0
 

Author Comment

by:victory_in
ID: 26075928
Hi flow01:,

Thanks. Which means it is better practive to pass the SQLCODE to the SQLERRM always i.e. SQLERRM(SQLCODE)  to get the correct message?

Thanks
0
 
LVL 21

Expert Comment

by:flow01
ID: 26076073
No, not in my opinion: for general exception handling sqlerrm does what I need with less coding.
And if ORACLE wants to keep their customers happy they will not change the current behaviour: it's probably used in many many thousand lines of code.
0
Industry Leaders: 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!

 

Author Comment

by:victory_in
ID: 26076259
flow01:,

Still I'm back to square. then when to use SQLERRM & SQLERRM(SQLCODE)? in what circumstances?

Thanks
0
 
LVL 21

Expert Comment

by:flow01
ID: 26076543
In all circumstances I know off the result is equal.
0
 

Author Comment

by:victory_in
ID: 26076843
Hi flow01:,

You mean we can use SQLERRM !!!  whatever!!!

Thanks a lot.
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 600 total points
ID: 26077082
SQLERRM is functionally equivalent to SQLERRM(SQLCODE)  because that's what it invokes


lookin SYS.STANDARD for the definition of SQLERRM (with no parameters)

you'll find this...


function sqlerrm return varchar2 is
    n1 number;
  begin
    n1 := sqlcode;
    return sqlerrm(n1);
  end sqlerrm;
0
 

Author Comment

by:victory_in
ID: 26077454
Hi all,

Ultimately I can use either SQLERRM alone or SQLERRM(SQLCODE). Both are same. OK. Now we are all on the same page. Agree?

But still one more concern in the above function (ID: 26077082), if we pass SQLCODE then also it works.? There is no IN parameters passed. Then how this SQLERRM(SQLCODE) works?

I didn't test this one as am @ home. I'll test this one tommorrow!!!

Thanks
Victor
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 26077539
SQLERRM()  with parameters is a built in function


if you call SQLERRM without parameters you'll call the one defined in SYS.STANDARD
if you call it with parameters you'll invoke the built in function (which is how the SYS.STANDARD function is able to work)



0
 

Author Comment

by:victory_in
ID: 26077804
Ok Sir. Agreed!!!

Thanks all for your wonderful support.

Victor
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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.
This video shows how to recover a database from a user managed backup
Suggested Courses

834 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