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('SQLE RRM: '||SQLERRM);
DBMS_OUTPUT.PUT_LINE('SQLC ODE: '||SQLCODE);
DBMS_OUTPUT.PUT_LINE('SQLC ODE: '||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
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('SQLE
DBMS_OUTPUT.PUT_LINE('SQLC
DBMS_OUTPUT.PUT_LINE('SQLC
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
flow01:,
Still I'm back to square. then when to use SQLERRM & SQLERRM(SQLCODE)? in what circumstances?
Thanks
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.
ASKER
Hi flow01:,
You mean we can use SQLERRM !!! whatever!!!
Thanks a lot.
You mean we can use SQLERRM !!! whatever!!!
Thanks a lot.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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)
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)
ASKER
Ok Sir. Agreed!!!
Thanks all for your wonderful support.
Victor
Thanks all for your wonderful support.
Victor
ASKER
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