Link to home
Start Free TrialLog in
Avatar of victory_in
victory_in

asked on

SQLERRM & SQLCODE

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
ASKER CERTIFIED SOLUTION
Avatar of flow01
flow01
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of victory_in
victory_in

ASKER

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
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.
flow01:,

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

Thanks
In all circumstances I know off the result is equal.
Hi flow01:,

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

Thanks a lot.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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)



Ok Sir. Agreed!!!

Thanks all for your wonderful support.

Victor