[MDEV-10840] sql_mode=ORACLE: RAISE statement for predefined exceptions - Jira

Alexander Barkov created issue - 2016-09-20 12:20

Alexander Barkov made changes - 2016-09-21 09:38

Field	Original Value	New Value
Summary	sql_mode=ORACLE: RAISE statement	sql_mode=ORACLE: RAISE statement for pre-defined exceptions

Alexander Barkov made changes - 2016-09-21 09:39

Description

Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}

Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

Alexander Barkov made changes - 2016-09-21 09:39

Summary

sql_mode=ORACLE: RAISE statement for pre-defined exceptions

sql_mode=ORACLE: RAISE statement for predefined exceptions

Alexander Barkov made changes - 2016-09-21 09:40

Description

Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

{{NO_DATA_FOUND}} will be translated to MariaDB warning.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silenty.
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

Alexander Barkov made changes - 2016-09-21 09:40

Description

Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

{{NO_DATA_FOUND}} will be translated to MariaDB warning.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silenty.
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

{{NO_DATA_FOUND}} will be translated to MariaDB warning.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silenty.
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

Alexander Barkov made changes - 2016-09-21 09:41

Description

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

{{NO_DATA_FOUND}} will be translated to MariaDB warning.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silenty.
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

{{NO_DATA_FOUND}} will be translated to MariaDB warning {{NO_DATA_FOUND}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silenty.
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

Alexander Barkov made changes - 2016-09-21 09:41

Description

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

{{NO_DATA_FOUND}} will be translated to MariaDB warning {{NO_DATA_FOUND}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silenty.
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{NO_DATA_FOUND}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silenty.
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

Alexander Barkov made changes - 2016-09-21 09:41

Description

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{NO_DATA_FOUND}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silenty.
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{NO_DATA_FOUND}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

Alexander Barkov made changes - 2016-09-21 09:45

Description

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{NO_DATA_FOUND}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{NO_DATA_FOUND}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

Alexander Barkov made changes - 2016-09-21 09:47

Description

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{NO_DATA_FOUND}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{ER_SP_FETCH_NO_DATA}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

Alexander Barkov made changes - 2016-09-21 09:50

Description

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{ER_SP_FETCH_NO_DATA}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{ER_SP_FETCH_NO_DATA}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

h2. {{DUP_VAL_ON_INDEX}}

Oracle's {{DUP_VAL_ON_INDEX}} will be translated to MariaDB error {{ER_DUP_ENTRY}}.

The following three scripts return an error:
{noformat}
ORA-00001: unique constraint ... violated
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE DUP_VAL_ON_INDEX;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN RAISE;
END;
/
CALL p1();
{code}

Alexander Barkov made changes - 2016-09-21 11:30

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2016-09-21 11:30

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

Sergei Golubchik made changes - 2016-09-22 15:53

Labels

Compatibility

Alexander Barkov made changes - 2016-09-27 06:20

Description

_emphasized text_Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{ER_SP_FETCH_NO_DATA}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

h2. {{DUP_VAL_ON_INDEX}}

Oracle's {{DUP_VAL_ON_INDEX}} will be translated to MariaDB error {{ER_DUP_ENTRY}}.

The following three scripts return an error:
{noformat}
ORA-00001: unique constraint ... violated
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE DUP_VAL_ON_INDEX;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN RAISE;
END;
/
CALL p1();
{code}

Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{ER_SP_FETCH_NO_DATA}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

h2. {{DUP_VAL_ON_INDEX}}

Oracle's {{DUP_VAL_ON_INDEX}} will be translated to MariaDB error {{ER_DUP_ENTRY}}.

The following three scripts return an error:
{noformat}
ORA-00001: unique constraint ... violated
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE DUP_VAL_ON_INDEX;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN RAISE;
END;
/
CALL p1();
{code}

Alexander Barkov made changes - 2016-09-27 08:06

Description

Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{ER_SP_FETCH_NO_DATA}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

h2. {{DUP_VAL_ON_INDEX}}

Oracle's {{DUP_VAL_ON_INDEX}} will be translated to MariaDB error {{ER_DUP_ENTRY}}.

The following three scripts return an error:
{noformat}
ORA-00001: unique constraint ... violated
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE DUP_VAL_ON_INDEX;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN RAISE;
END;
/
CALL p1();
{code}

Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~ and ~~MDEV-10582~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX
- INVALID_CURSOR

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{ER_SP_FETCH_NO_DATA}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

h2. {{DUP_VAL_ON_INDEX}}

Oracle's {{DUP_VAL_ON_INDEX}} will be translated to MariaDB error {{ER_DUP_ENTRY}}.

The following three scripts return an error:
{noformat}
ORA-00001: unique constraint ... violated
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE DUP_VAL_ON_INDEX;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN RAISE;
END;
/
CALL p1();
{code}

Alexander Barkov made changes - 2016-09-27 08:07

Description

Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~ and ~~MDEV-10582~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX
- INVALID_CURSOR

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{ER_SP_FETCH_NO_DATA}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

h2. {{DUP_VAL_ON_INDEX}}

Oracle's {{DUP_VAL_ON_INDEX}} will be translated to MariaDB error {{ER_DUP_ENTRY}}.

The following three scripts return an error:
{noformat}
ORA-00001: unique constraint ... violated
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE DUP_VAL_ON_INDEX;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN RAISE;
END;
/
CALL p1();
{code}

Implement the {{RAISE}} statement:
{code:sql}
RAISE [exception_name];
{code}
where {{exceptions_name}} is one of those implemented in ~~MDEV-10839~~ and ~~MDEV-10582~~:
- NO_DATA_FOUND
- TOO_MANY_ROWS
- DUP_VAL_ON_INDEX
- INVALID_CURSOR

h2. {{NO_DATA_FOUND}}

Oracle's {{NO_DATA_FOUND}} will be translated to MariaDB warning {{ER_SP_FETCH_NO_DATA}}.

The following three scripts do not return any errors, which proves that {{NO_DATA_FOUND}} is cought and raised silently and therefore is more like a warning than an error:
{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE NO_DATA_FOUND;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE;
END;
/
CALL p1();
{code}

This script demonstrates that {{NO_DATA_FOUND}} is actually cought and can be translated to a fatal error using an {{EXCEPTION..RAISE}} statement.
{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
CREATE PROCEDURE p1
AS
  a INT;
  e EXCEPTION;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN NO_DATA_FOUND THEN RAISE e;
END;
/
CALL p1();
{code}

h2. {{TOO_MANY_ROWS}}

Oracle's {{TOO_MANY_ROWS}} will be translated to MariaDB error {{TOO_MANY_ROWS}}.

The following three scripts return an error:
{noformat}
ORA-01422: exact fetch returns more than requested number of rows
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE TOO_MANY_ROWS;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (10);
INSERT INTO t1 VALUES (20);
CREATE PROCEDURE p1
AS
  a INT;
BEGIN
  SELECT a INTO a FROM t1;
EXCEPTION
  WHEN TOO_MANY_ROWS THEN RAISE;
END;
/
CALL p1();
{code}

h2. {{DUP_VAL_ON_INDEX}}

Oracle's {{DUP_VAL_ON_INDEX}} will be translated to MariaDB error {{ER_DUP_ENTRY}}.

The following three scripts return an error:
{noformat}
ORA-00001: unique constraint ... violated
{noformat}

{code:sql}
DROP PROCEDURE p1;
CREATE PROCEDURE p1
AS
BEGIN
  RAISE DUP_VAL_ON_INDEX;
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
END;
/
CALL p1();
{code}

{code:sql}
DROP TABLE t1;
DROP PROCEDURE p1;
CREATE TABLE t1 (a INT PRIMARY KEY);
CREATE PROCEDURE p1
AS
BEGIN
  INSERT INTO t1 VALUES (10);
  INSERT INTO t1 VALUES (10);
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN RAISE;
END;
/
CALL p1();
{code}

h2. {{INVALID_CURSOR}}

Oracle's {{INVALID_CURSOR}} will be translated to MariaDB error {{ER_SP_CURSOR_NOT_OPEN}}.

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

Labels

Compatibility

Compatibility NRE-307517

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

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:30

Labels

Approved Compatibility

Compatibility

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

Labels

Compatibility

Compatibility need_review

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

Component/s

Stored routines [ 13905 ]

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 [ 77141 ]

MariaDB v4 [ 150935 ]

MariaDB Server

sql_mode=ORACLE: RAISE statement for predefined exceptions

Details

Description

`NO_DATA_FOUND`

`TOO_MANY_ROWS`

`DUP_VAL_ON_INDEX`

`INVALID_CURSOR`

Attachments

Activity

People

Dates

Git Integration