[MDEV-10578] sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM - Jira

XML

Word

Printable

Details

Type: Technical task
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: None
Fix Version/s: 10.3.0
Component/s: Parser, Stored routines
Labels:
- Compatibility

Sprint:
10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

Description

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

Attachments

Issue Links

relates to

MDEV-11022 sql_mode=ORACLE: SQLERRM(errcode)

Open

MDEV-10576 sql_mode=ORACLE: Functions with no parameters can be called without parentheses

Open

Activity

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2016-08-17 15:11

Updated:: 2020-08-27 07:12

Resolved:: 2016-10-11 08:26

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.