When an error occurs during database or PLSQL running, an exception is automatically thrown by the engine when PLSQL is running. oracle has three methods to throw an exception. If you need to know about it, you can read this article.
When an error occurs during database or PL/SQL running, an exception is automatically thrown by the engine during PL/SQL running. oracle has three ways to throw an exception. If you need to know about it, you can read this article.
There are three ways to throw an exception
1. Use the PL/SQL Runtime Engine
2. Use the RAISE statement
3. Call the Stored Procedure RAISE_APPLICATION_ERROR.
When an error occurs during database or PL/SQL running, an exception is automatically thrown by the PL/SQL runtime engine. An exception can also be thrown through the RAISE statement.
RAISE prediction_name;
Explicit throws are a habit of programmers to handle declared exceptions, but RAISE is not limited to declared exceptions. It can throw any exceptions. For example, if you want to use TIMEOUT_ON_RESOURCE error to detect a new runtime exception processor, you only need to use the following statement in the program:
RAISE TIMEOUT_ON_RESOUCE;
For example, in the following order Input example, if the order is smaller than the inventory quantity, an exception is thrown, and the exception is caught to handle the exception.
The Code is as follows:
DECLARE
Inventory_too_low EXCEPTION;
--- Other statements
BEGIN
IF order_rec.qty> inventory_rec.qty THEN
RAISE inventory_too_low;
END IF
EXCEPTION
WHEN inventory_too_low THEN
Order_rec.staus: = 'backordered ';
END;
The RAISE_APPLICATION_ERROR built-in function is used to throw an exception and assign an error number and error message to the exception. The default error code for custom exceptions is + 1, and the default information is User_Defined_Exception. The RAISE_APPLICATION_ERROR function can be called in the execution part and exception part of the pl/SQL program block, explicitly throwing a naming exception with a special error code. Raise_application_error (error_number, message [, true, false])
The error number ranges from-20,000 to-20,999. The error message is a text string, which consists of up to 2048 bytes. TRUE and FALSE indicate whether to add (TRUE) to the error stack or overwrite (FALSE ). The default value is FALSE.
The following code is used:
The Code is as follows:
IF product_not_found THEN
RAISE_APPLICATION_ERROR (-20123, 'invald product Code', TRUE );
End if;
Bytes --------------------------------------------------------------------------------------------------
When an exception is thrown, the control is unconditionally transferred to the exception part, which means that the control cannot return to the exception location. After the exception is handled and resolved, control the next statement returned to the execution part of the previous layer.
The Code is as follows:
BEGIN
DECLARE
Bad_credit exception;
BEGIN
RAISE bad_credit;
-- If an exception occurs, control the redirection;
EXCEPTION
WHEN bad_credit THEN
Dbms_output.put_line ('bad _ credentials ');
END;
-- After bad_credit exception handling, the control goes here
EXCEPTION
WHEN OTHERS THEN
-- Control will not go from bad_credit exception to here
-- Because bad_credit has been processed
END;
When an exception occurs and there is no such exception processor in the block, the control will go to or spread to the exception handling part of the previous block.
BEGIN
DECLARE --- internal block start
Bad_credit exception;
BEGIN
RAISE bad_credit;
-- If an exception occurs, control the redirection;
EXCEPTION
WHEN ZERO_DIVIDE THEN -- cannot handle bad_credite exceptions
Dbms_output.put_line ('ide by zero error ');
END -- END the internal Block
-- The control cannot be reached here because the exception is not resolved;
-- Exception
EXCEPTION
WHEN OTHERS THEN
-- Since bad_credit is not resolved, control will go here
END;
