SQLCODE
The function SQLCODE returns the number code of the most recent exception.
Outside of SP the function SQLCODE is not available and is treated as a normal identifier.
Inside an SP the function SQLCODE can be hidden by a user-defined identifier visible in the current context:
This example returns the error code normally:
DROP FUNCTION f1;
|
CREATE FUNCTION f1 RETURN VARCHAR AS BEGIN RETURN SQLCODE; END;
|
/
|
SELECT f1() FROM DUAL;
|
This example returns the value of the column t1.sqlcode:
DROP TABLE t1;
|
CREATE TABLE t1 (SQLCODE INT);
|
INSERT INTO t1 VALUES (10);
|
COMMIT;
|
DROP FUNCTION f1;
|
CREATE FUNCTION f1 RETURN NUMBER AS
|
v INT;
|
BEGIN
|
SELECT MIN(SQLCODE) INTO v FROM t1;
|
RETURN v;
|
END;
|
/
|
SELECT f1 FROM dual;
|
This example returns the value of the variable SQLCODE:
DROP FUNCTION f1;
|
CREATE FUNCTION f1 RETURN VARCHAR AS
|
SQLCODE INT:=123;
|
BEGIN
|
RETURN SQLCODE;
|
END;
|
/
|
SELECT f1 FROM dual;
|
SQLERRM
The function SQLERRM returns the error message associated with its error-number argument. If the argument is omitted, it returns the error message associated with the current value of SQLCODE
The function SQLERRM is not available outside of an SP and SQLERRM is treated as a normal identifier.
Warning-alike errors
NO_DATA_FOUND is more like MariaDB warning. It should be correctly handled by SQLCODE and SQLERRM:
DROP TABLE t1;
|
DROP FUNCTION f1;
|
CREATE TABLE t1 (a INT);
|
CREATE FUNCTION f1 RETURN VARCHAR
|
AS
|
a INT;
|
BEGIN
|
SELECT a INTO a FROM t1;
|
RETURN 'No exception ' || TO_CHAR(SQLCODE) || ' ' || SQLERRM;
|
EXCEPTION
|
WHEN OTHERS THEN
|
RETURN 'Exception ' || TO_CHAR(SQLCODE) || ' ' || SQLERRM;
|
END;
|
/
|
SELECT f1 FROM DUAL;
|
F1
|
--------------------------------------------------------------------------------
|
Exception 100 ORA-01403: no data found
|
SQLCODE and SQLERRM should be cleared on functions return
DROP TABLE t1;
|
DROP FUNCTION f1;
|
DROP FUNCTION f2;
|
CREATE TABLE t1 (a INT);
|
CREATE FUNCTION f1 RETURN VARCHAR
|
AS
|
a INT:=10;
|
BEGIN
|
SELECT a INTO a FROM t1;
|
RETURN 'Value='|| TO_CHAR(a);
|
EXCEPTION
|
WHEN OTHERS THEN RETURN 'Exception|' || SQLCODE || ' ' || SQLERRM;
|
END;
|
/
|
CREATE FUNCTION f2 RETURN VARCHAR
|
AS
|
a VARCHAR(128);
|
BEGIN
|
RETURN f1 || '|' || SQLCODE || ' ' || SQLERRM;
|
END;
|
/
|
SELECT f2 FROM DUAL;
|
F2
|
--------------------------------------------------------------------------------
|
Exception|100 ORA-01403: no data found|0 ORA-0000: normal, successful completion
|
Notice, the calls for SQLCODE and SQLERRM in f2 generated 0 ORA-0000: normal, successful completion
The behavior is the same on a procedure return:
DROP TABLE t1;
|
DROP PROCEDURE p1;
|
DROP FUNCTION f2;
|
CREATE TABLE t1 (a INT);
|
CREATE PROCEDURE p1(res OUT VARCHAR)
|
AS
|
a INT:=10;
|
BEGIN
|
SELECT a INTO a FROM t1;
|
res:='Value='|| TO_CHAR(a);
|
EXCEPTION
|
WHEN OTHERS THEN res:='Exception|' || SQLCODE || ' ' || SQLERRM;
|
END;
|
/
|
CREATE FUNCTION f2 RETURN VARCHAR
|
AS
|
res VARCHAR(128);
|
BEGIN
|
p1(res);
|
RETURN res || '|' || SQLCODE || ' ' || SQLERRM;
|
END;
|
/
|
SELECT f2() FROM DUAL;
|
F2()
|
--------------------------------------------------------------------------------
|
Exception|100 ORA-01403: no data found|0 ORA-0000: normal, successful completion
|
Limitations
- This task will add SQLCODE and SQLERRM with no parentheses
- This task will allow SQLCODE and SQLERRM to be shadowed by SP variables only, but not table columns.
Further related tasks
- Calling SQLCODE and SQLERRM with empty parentheses will be done in MDEV-10576
- Calling SQLERRM with an error number argument will be done separately in MDEV-11022
- Shadowing SQLCODE and SQLERRM functions by table column names will be done in MDEV-10576
|
[MDEV-10578]