Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-10578

sql_mode=ORACLE: SP control functions SQLCODE, SQLERRM

    Details

    • 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

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: