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

sql_mode=ORACLE: User defined exceptions

    XMLWordPrintable

    Details

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

      Description

      When running sql_mode=ORACLE, MariaDB should support:

      • user defined EXCEPTION declaration
      • using user defined exceptions in RAISE name (signal)
      • using user defined exceptions in RAISE (resignal)
      • using user defined exceptions in EXCEPTION WHEN

      DROP FUNCTION f1;
      CREATE FUNCTION f1 (a INT) RETURN INT
      AS
        e1 EXCEPTION;
      BEGIN
        IF a < 0 THEN
          RAISE e1;  
        END IF;
        RETURN 0;
      EXCEPTION  
        WHEN e1 THEN RETURN 1;
      END;
      /
      

      User defined exception names should be case insensitive.

      Details

      MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:

      SET sql_mode=DEFAULT;
      DROP PROCEDURE IF EXISTS p1;
      DELIMITER $$
      CREATE PROCEDURE p1()
      BEGIN
        DECLARE c0 CONDITION FOR SQLSTATE '45000';
        DECLARE c1 CONDITION FOR SQLSTATE '45000';
        DECLARE CONTINUE HANDLER FOR c0 SET @c0= 0;
        DECLARE CONTINUE HANDLER FOR c1 SET @c0= 1;
      END;
      $$
      

      ERROR 1413 (42000): Duplicate handler declared in the same block
      

      In sql_mode=ORACLE it will be possible to have multiple user-defined exceptions in the same block. All user defined exceptions will be associated with SQLSTATE '45000' and MySQL errno ER_SIGNAL_EXCEPTION

      SET sql_mode=ORACLE;
      DELIMITER $$;
      CREATE FUNCTION f1(c VARCHAR) RETURN VARCHAR
      AS
        e EXCEPTION;
        f EXCEPTION;
        a VARCHAR(64):='';
      BEGIN
        BEGIN
          IF c = 'e' THEN RAISE e; END IF;
          IF c = 'f' THEN RAISE f; END IF;
        EXCEPTION
          WHEN e THEN BEGIN a:='Got EXCEPTION1/e; '; RAISE e; END;
          WHEN f THEN BEGIN a:='Got EXCEPTION1/f; '; RAISE f; END;
        END;
        RETURN 'Got no exceptions';
      EXCEPTION
        WHEN OTHERS THEN RETURN a || 'Got EXCEPTION2/OTHERS;';
      END;
      $$
      

      Notice, both e and f are associated with the same SQLSTATE and errno, but having WHEN e followed by WHEN f is valid, because e and f are different exceptions.

      However, specifying the same exception multiple times in WHEN clause will not be possible:

      SET sql_mode=ORACLE;
      DELIMITER $$;
      CREATE FUNCTION f1() RETURN VARCHAR
      AS
        e EXCEPTION;
      BEGIN
        RETURN 'Got no exceptions';
      EXCEPTION
        WHEN e THEN RETURN 'Got exception e';
        WHEN e THEN RETURN 'Got exception e';
      END;
      $$
      

      The above script will return the ER_SP_DUP_HANDLER error:

      ERROR 42000: Duplicate handler declared in the same block
      

      Name space

      In MariaDB variables and exceptions (conditions) are in separate name spaces. Oracle-alike user defined exceptions will be in the same name space with MariaDB conditions.
      In Oracle, variables and exceptions (conditions) are in the same name space. Placing exceptions and variables into the same name space will be done separately. See MDEV-11058.

        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: