Discussion
When porting stored procedures from Oracle to MySQL, the Oracle specific function RAISE_APPLICATION_ERROR(code, message) is being used to transport an error code and an error message to the application and stop execution of the
procedure. MySQL does not currently have support for SIGNAL, so this functionality must be emulated.
The function raise_application_error(code, message) in Oracle terminates the execution of the current function or procedure, and raises a given error condition. MySQL does not yet implement SIGNAL, so that a direct port is not possible. The following function terminates execution of a function or procedure with an error, and delivers the error code and message as part of the result set, since without SIGNAL we cannot influence SQLSTATE or MySQL error codes internally. The error code shown is 1406, SQLSTATE 22001.DROP PROCEDURE IF EXISTS raise_application_error;
DELIMETER $$
CREATE PROCEDURE raise_application_error(
IN CODE INTEGER,
IN MESSAGE VARCHAR(255)
)
SQL SECURITY INVOKER
DETERMINISTIC
BEGIN
DECLARE ERROR CHAR(2);
SELECT CODE, MESSAGE;
SET ERROR = 'abc';
END;
$$
DELIMETER ;
Below is an example of code using this stored procedure:root@localhost [boom]> CALL raise_application_error(0815,
'something went wrong, badly.');
+------+------------------------------+
| code | message |
+------+------------------------------+
| 815 | something went wrong, badly. |
+------+------------------------------+
ERROR 1406 (22001): Data too long for column 'error' at row 1