[MDEV-10587] sql_mode=ORACLE: User defined exceptions - Jira

Alexander Barkov created issue - 2016-08-18 04:08

Alexander Barkov made changes - 2016-08-18 04:09

Field	Original Value	New Value
Description	When running {{sql_mode=ORACLE}, MariaDB should support: - user defined {{EXCEPTION}} declaration - using user defined exceptions in {{RAISE}} {code:sql} 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; / {code}	When running {{sql_mode=ORACLE}, MariaDB should support: - user defined {{EXCEPTION}} declaration - using user defined exceptions in {{RAISE}} - using user defined exceptions in {{EXCEPTION WHEN}} {code:sql} 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; / {code}

Michael Widenius made changes - 2016-08-18 12:50

Parent		~~MDEV-10142~~ [ 56873 ]
Issue Type	Task [ 3 ]	Technical task [ 7 ]

Alvin Richards (Inactive) made changes - 2016-08-18 16:27

Labels

Compatibility

Alvin Richards (Inactive) made changes - 2016-08-18 16:33

Labels

Compatibility

Alexander Barkov made changes - 2016-08-30 13:25

Assignee

Alexander Barkov [ bar ]

Alvin Richards (Inactive) made changes - 2016-09-01 07:19

Labels

Compatibility

Alvin Richards (Inactive) made changes - 2016-09-01 17:29

Labels

Compatibility

Sergei Golubchik made changes - 2016-09-20 16:45

Labels

Compatibility

Sergei Golubchik made changes - 2016-09-20 16:45

Description

When running {{sql_mode=ORACLE}, MariaDB should support:
- user defined {{EXCEPTION}} declaration
- using user defined exceptions in {{RAISE}}
- using user defined exceptions in {{EXCEPTION WHEN}}

{code:sql}
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;
/
{code}

When running {{sql_mode=ORACLE}}, MariaDB should support:
- user defined {{EXCEPTION}} declaration
- using user defined exceptions in {{RAISE}}
- using user defined exceptions in {{EXCEPTION WHEN}}

{code:sql}
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;
/
{code}

Alexander Barkov made changes - 2016-10-11 10:18

Description

When running {{sql_mode=ORACLE}}, MariaDB should support:
- user defined {{EXCEPTION}} declaration
- using user defined exceptions in {{RAISE}}
- using user defined exceptions in {{EXCEPTION WHEN}}

{code:sql}
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;
/
{code}

When running {{sql_mode=ORACLE}}, MariaDB should support:
- user defined {{EXCEPTION}} declaration
- using user defined exceptions in {{RAISE}}
- using user defined exceptions in {{EXCEPTION WHEN}}

{code:sql}
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;
/
{code}

Variables and exceptions are in the same name space. Having a variable and an exception with the same names is not possible in the same scope:
{code:sql}
DROP FUNCTION f1;
CREATE FUNCTION f1 RETURN VARCHAR
AS
  e INT:=10;
  e EXCEPTION;
BEGIN
  RAISE e;
END;
/
SHOW ERRORS;
{code}
{noformat}
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: Statement ignored
6/9 PLS-00371: at most one declaration for 'E' is permitted
{noformat}

Alexander Barkov made changes - 2016-10-11 10:18

Description

When running {{sql_mode=ORACLE}}, MariaDB should support:
- user defined {{EXCEPTION}} declaration
- using user defined exceptions in {{RAISE}}
- using user defined exceptions in {{EXCEPTION WHEN}}

{code:sql}
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;
/
{code}

Variables and exceptions are in the same name space. Having a variable and an exception with the same names is not possible in the same scope:
{code:sql}
DROP FUNCTION f1;
CREATE FUNCTION f1 RETURN VARCHAR
AS
  e INT:=10;
  e EXCEPTION;
BEGIN
  RAISE e;
END;
/
SHOW ERRORS;
{code}
{noformat}
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: Statement ignored
6/9 PLS-00371: at most one declaration for 'E' is permitted
{noformat}

When running {{sql_mode=ORACLE}}, MariaDB should support:
- user defined {{EXCEPTION}} declaration
- using user defined exceptions in {{RAISE}}
- using user defined exceptions in {{EXCEPTION WHEN}}

{code:sql}
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;
/
{code}

Variables and exceptions are in the same name space. Having a variable and an exception with the same name is not possible in the same scope:
{code:sql}
DROP FUNCTION f1;
CREATE FUNCTION f1 RETURN VARCHAR
AS
  e INT:=10;
  e EXCEPTION;
BEGIN
  RAISE e;
END;
/
SHOW ERRORS;
{code}
{noformat}
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: Statement ignored
6/9 PLS-00371: at most one declaration for 'E' is permitted
{noformat}

Alexander Barkov made changes - 2016-10-11 11:12

Description

When running {{sql_mode=ORACLE}}, MariaDB should support:
- user defined {{EXCEPTION}} declaration
- using user defined exceptions in {{RAISE}}
- using user defined exceptions in {{EXCEPTION WHEN}}

{code:sql}
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;
/
{code}

Variables and exceptions are in the same name space. Having a variable and an exception with the same name is not possible in the same scope:
{code:sql}
DROP FUNCTION f1;
CREATE FUNCTION f1 RETURN VARCHAR
AS
  e INT:=10;
  e EXCEPTION;
BEGIN
  RAISE e;
END;
/
SHOW ERRORS;
{code}
{noformat}
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: Statement ignored
6/9 PLS-00371: at most one declaration for 'E' is permitted
{noformat}

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}} (resignale)
- using user defined exceptions in {{EXCEPTION WHEN}}

{code:sql}
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;
/
{code}

Variables and exceptions are in the same name space. Having a variable and an exception with the same name is not possible in the same scope:
{code:sql}
DROP FUNCTION f1;
CREATE FUNCTION f1 RETURN VARCHAR
AS
  e INT:=10;
  e EXCEPTION;
BEGIN
  RAISE e;
END;
/
SHOW ERRORS;
{code}
{noformat}
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: Statement ignored
6/9 PLS-00371: at most one declaration for 'E' is permitted
{noformat}

Alexander Barkov made changes - 2016-10-11 11:12

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}} (resignale)
- using user defined exceptions in {{EXCEPTION WHEN}}

{code:sql}
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;
/
{code}

Variables and exceptions are in the same name space. Having a variable and an exception with the same name is not possible in the same scope:
{code:sql}
DROP FUNCTION f1;
CREATE FUNCTION f1 RETURN VARCHAR
AS
  e INT:=10;
  e EXCEPTION;
BEGIN
  RAISE e;
END;
/
SHOW ERRORS;
{code}
{noformat}
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: Statement ignored
6/9 PLS-00371: at most one declaration for 'E' is permitted
{noformat}

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}}

{code:sql}
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;
/
{code}

Variables and exceptions are in the same name space. Having a variable and an exception with the same name is not possible in the same scope:
{code:sql}
DROP FUNCTION f1;
CREATE FUNCTION f1 RETURN VARCHAR
AS
  e INT:=10;
  e EXCEPTION;
BEGIN
  RAISE e;
END;
/
SHOW ERRORS;
{code}
{noformat}
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: Statement ignored
6/9 PLS-00371: at most one declaration for 'E' is permitted
{noformat}

Alexander Barkov made changes - 2016-10-11 12:55

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}}

{code:sql}
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;
/
{code}

Variables and exceptions are in the same name space. Having a variable and an exception with the same name is not possible in the same scope:
{code:sql}
DROP FUNCTION f1;
CREATE FUNCTION f1 RETURN VARCHAR
AS
  e INT:=10;
  e EXCEPTION;
BEGIN
  RAISE e;
END;
/
SHOW ERRORS;
{code}
{noformat}
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: Statement ignored
6/9 PLS-00371: at most one declaration for 'E' is permitted
{noformat}

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

Variables and exceptions are in the same name space. Having a variable and an exception with the same name is not possible in the same scope:
{code:sql}
DROP FUNCTION f1;
CREATE FUNCTION f1 RETURN VARCHAR
AS
  e INT:=10;
  e EXCEPTION;
BEGIN
  RAISE e;
END;
/
SHOW ERRORS;
{code}
{noformat}
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: Statement ignored
6/9 PLS-00371: at most one declaration for 'E' is permitted
{noformat}

Alexander Barkov made changes - 2016-10-12 13:17

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2016-10-12 13:26

Link

This issue is blocked by ~~MDEV-11037~~ [ ~~MDEV-11037~~ ]

Rasmus Johansson (Inactive) made changes - 2016-10-13 06:22

Parent	~~MDEV-10142~~ [ 56873 ]
Issue Type	Technical task [ 7 ]	Task [ 3 ]

Rasmus Johansson (Inactive) made changes - 2016-10-13 06:22

Epic Link

MDEV-10137 [ 56868 ]

Rasmus Johansson (Inactive) made changes - 2016-10-13 06:36

Sprint

10.2.3-2 [ 105 ]

Rasmus Johansson (Inactive) made changes - 2016-10-13 06:36

Rank

Ranked lower

Alexander Barkov made changes - 2016-10-14 12:55

Link

This issue relates to MDEV-11058 [ MDEV-11058 ]

Alexander Barkov made changes - 2016-10-14 12:59

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

Variables and exceptions are in the same name space. Having a variable and an exception with the same name is not possible in the same scope:
{code:sql}
DROP FUNCTION f1;
CREATE FUNCTION f1 RETURN VARCHAR
AS
  e INT:=10;
  e EXCEPTION;
BEGIN
  RAISE e;
END;
/
SHOW ERRORS;
{code}
{noformat}
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PL/SQL: Statement ignored
6/9 PLS-00371: at most one declaration for 'E' is permitted
{noformat}

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

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

Alexander Barkov made changes - 2016-10-14 13:04

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

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

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. 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.

Alexander Barkov made changes - 2016-10-14 13:07

issue.field.resolutiondate

2016-10-14 13:07:25.0

2016-10-14 13:07:25.166

Alexander Barkov made changes - 2016-10-14 13:07

Fix Version/s		10.3.0 [ 22127 ]
Fix Version/s	10.2 [ 14601 ]
Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Rasmus Johansson (Inactive) made changes - 2016-10-14 13:23

Parent		~~MDEV-10142~~ [ 56873 ]
Issue Type	Task [ 3 ]	Technical task [ 7 ]

Rasmus Johansson (Inactive) made changes - 2016-10-14 13:23

Epic Link

MDEV-10137 [ 56868 ]

Alexander Barkov made changes - 2016-10-14 14:26

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. 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.

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

Note, MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}

{code}

h2. 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.

Alexander Barkov made changes - 2016-10-14 14:39

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

Note, MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}

{code}

h2. 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.

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. Details
MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}
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;
$$
{code}
{noformat}
ERROR 1413 (42000): Duplicate handler declared in the same block
{noformat}

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}}
{code:sql}
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;
$$
{code}
Notice, both {{e}} and {{f}} are associated with the same SQLSTATE and errno, but having {{WHEN e}} followed by {{WHEN f}} is valid, because they are different exceptions.

However, specifying the same exception multiple times in {{WHEN}} clause will not be possible:
{code:sql}
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;
$$
{code}
The above script will return the {{ER_SP_DUP_HANDLER}} error:
{noformat}
ERROR 42000: Duplicate handler declared in the same block
{noformat}

{code}

h2. 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.

Alexander Barkov made changes - 2016-10-14 14:41

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. Details
MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}
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;
$$
{code}
{noformat}
ERROR 1413 (42000): Duplicate handler declared in the same block
{noformat}

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}}
{code:sql}
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;
$$
{code}
Notice, both {{e}} and {{f}} are associated with the same SQLSTATE and errno, but having {{WHEN e}} followed by {{WHEN f}} is valid, because they are different exceptions.

However, specifying the same exception multiple times in {{WHEN}} clause will not be possible:
{code:sql}
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;
$$
{code}
The above script will return the {{ER_SP_DUP_HANDLER}} error:
{noformat}
ERROR 42000: Duplicate handler declared in the same block
{noformat}

{code}

h2. 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.

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. Details
MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}
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;
$$
{code}
{noformat}
ERROR 1413 (42000): Duplicate handler declared in the same block
{noformat}

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}}
{code:sql}
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;
$$
{code}
Notice, both {{e}} and {{f}} are associated with the same SQLSTATE and errno, but having {{WHEN e}} followed by {{WHEN f}} is valid, because they are different exceptions.

However, specifying the same exception multiple times in {{WHEN}} clause will not be possible:
{code:sql}
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;
$$
{code}
The above script will return the {{ER_SP_DUP_HANDLER}} error:
{noformat}
ERROR 42000: Duplicate handler declared in the same block
{noformat}

h2. 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.

Alexander Barkov made changes - 2016-10-14 14:42

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. Details
MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}
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;
$$
{code}
{noformat}
ERROR 1413 (42000): Duplicate handler declared in the same block
{noformat}

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}}
{code:sql}
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;
$$
{code}
Notice, both {{e}} and {{f}} are associated with the same SQLSTATE and errno, but having {{WHEN e}} followed by {{WHEN f}} is valid, because they are different exceptions.

However, specifying the same exception multiple times in {{WHEN}} clause will not be possible:
{code:sql}
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;
$$
{code}
The above script will return the {{ER_SP_DUP_HANDLER}} error:
{noformat}
ERROR 42000: Duplicate handler declared in the same block
{noformat}

h2. 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.

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. Details
MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}
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;
$$
{code}
{noformat}
ERROR 1413 (42000): Duplicate handler declared in the same block
{noformat}

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}}
{code:sql}
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;
$$
{code}
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}} they are different exceptions.

However, specifying the same exception multiple times in {{WHEN}} clause will not be possible:
{code:sql}
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;
$$
{code}
The above script will return the {{ER_SP_DUP_HANDLER}} error:
{noformat}
ERROR 42000: Duplicate handler declared in the same block
{noformat}

h2. 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.

Alexander Barkov made changes - 2016-10-14 14:42

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. Details
MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}
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;
$$
{code}
{noformat}
ERROR 1413 (42000): Duplicate handler declared in the same block
{noformat}

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}}
{code:sql}
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;
$$
{code}
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}} they are different exceptions.

However, specifying the same exception multiple times in {{WHEN}} clause will not be possible:
{code:sql}
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;
$$
{code}
The above script will return the {{ER_SP_DUP_HANDLER}} error:
{noformat}
ERROR 42000: Duplicate handler declared in the same block
{noformat}

h2. 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.

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. Details
MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}
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;
$$
{code}
{noformat}
ERROR 1413 (42000): Duplicate handler declared in the same block
{noformat}

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}}
{code:sql}
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;
$$
{code}
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:
{code:sql}
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;
$$
{code}
The above script will return the {{ER_SP_DUP_HANDLER}} error:
{noformat}
ERROR 42000: Duplicate handler declared in the same block
{noformat}

h2. 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.

Alexander Barkov made changes - 2016-10-14 14:42

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. Details
MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}
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;
$$
{code}
{noformat}
ERROR 1413 (42000): Duplicate handler declared in the same block
{noformat}

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}}
{code:sql}
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;
$$
{code}
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:
{code:sql}
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;
$$
{code}
The above script will return the {{ER_SP_DUP_HANDLER}} error:
{noformat}
ERROR 42000: Duplicate handler declared in the same block
{noformat}

h2. 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.

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}}

{code:sql}
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;
/
{code}

User defined exception names should be case insensitive.

h2. Details
MariaDB does not support multiple handlers for the same SQLSTATE. This script returns an error:
{code:sql}
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;
$$
{code}
{noformat}
ERROR 1413 (42000): Duplicate handler declared in the same block
{noformat}

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}}
{code:sql}
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;
$$
{code}
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:
{code:sql}
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;
$$
{code}
The above script will return the {{ER_SP_DUP_HANDLER}} error:
{noformat}
ERROR 42000: Duplicate handler declared in the same block
{noformat}

h2. 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.

Alvin Richards (Inactive) made changes - 2016-11-07 23:32

Labels

Compatibility

Compatibility NRE-307517

Alvin Richards (Inactive) made changes - 2016-12-21 18:02

Labels

Compatibility NRE-307517

Approved Compatibility NRE-307517

Alvin Richards (Inactive) made changes - 2016-12-21 19:29

NRE Projects

NRE-307517

Alvin Richards (Inactive) made changes - 2016-12-21 19:40

Labels

Approved Compatibility NRE-307517

Approved Compatibility

Alvin Richards (Inactive) made changes - 2016-12-22 19:10

NRE Approved

Yes [ 10304 ]

Alvin Richards (Inactive) made changes - 2016-12-22 19:29

Labels

Approved Compatibility

Compatibility

Alexander Barkov made changes - 2017-03-10 11:53

Labels

Compatibility

Compatibility need_review

Alexander Barkov made changes - 2017-04-09 03:00

Component/s		Parser [ 10201 ]
Component/s		Stored routines [ 13905 ]
Component/s	OTHER [ 10125 ]

Sergei Golubchik made changes - 2020-08-27 07:12

Labels

Compatibility need_review

Compatibility

Sergei Golubchik made changes - 2021-12-06 21:43

Workflow

MariaDB v3 [ 76693 ]

MariaDB v4 [ 150776 ]

Alexander Barkov made changes - 2024-06-06 07:11

Link

This issue relates to ~~MDEV-20662~~ [ ~~MDEV-20662~~ ]

MariaDB Server

sql_mode=ORACLE: User defined exceptions

Details

Description

Details

Name space

Attachments

Issue Links

Activity

People

Dates

Git Integration