{"id":2831,"date":"2019-08-07T20:57:05","date_gmt":"2019-08-08T03:57:05","guid":{"rendered":"https:\/\/oracletutorial.com\/?page_id=2831"},"modified":"2025-05-24T20:01:17","modified_gmt":"2025-05-25T03:01:17","slug":"plsql-exception-propagation","status":"publish","type":"page","link":"https:\/\/www.oracletutorial.com\/plsql-tutorial\/plsql-exception-propagation\/","title":{"rendered":"PL\/SQL Exception Propagation"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about how PL\/SQL propagates an unhandled exception raised in the current block to the enclosing blocks.<\/p>\n\n\n\n

Introduction to PL\/SQL exception propagation #<\/a><\/h2>\n\n\n\n

When an exception<\/a> occurs, PL\/SQL looks for an exception handler in the current block e.g., an anonymous block<\/a>, procedure<\/a>, or function<\/a> of the exception. <\/p>\n\n\n\n

If it does not find a match, PL\/SQL propagates the exception to the enclosing block of the current block.<\/p>\n\n\n\n

PL\/SQL then attempts to handle the exception by raising it once more in the enclosing block. <\/p>\n\n\n\n

This process continues in each successive enclosing block until there is no remaining block in which to raise the exception<\/a>. <\/p>\n\n\n\n

If there is no exception handler in all blocks, PL\/SQL returns an unhandled exception to the application environment that executed the outermost PL\/SQL block.<\/p>\n\n\n\n

Note that an unhandled exception stops the execution of the block.<\/p>\n\n\n\n

Unhandled exception propagation examples #<\/a><\/h2>\n\n\n\n

See the following anonymous block:<\/p>\n\n\n

DECLARE<\/span>\n    e1 EXCEPTION<\/span>;\n    PRAGMA<\/span> exception_init (e1, -20001<\/span>);\n    e2 EXCEPTION;\n    PRAGMA<\/span> exception_init (e2, -20002<\/span>);\n    e3 EXCEPTION;\n    PRAGMA<\/span> exception_init (e3, -20003<\/span>);\n    l_input NUMBER := &input_number;\nBEGIN<\/span>\n    -- inner block<\/span>\n    BEGIN<\/span>\n        IF<\/span> l_input = 1<\/span> THEN<\/span>\n            raise_application_error(-20001<\/span>,'Exception: the input number is 1'<\/span>);\n        ELSIF l_input = 2 THEN\n            raise_application_error(-20002,'Exception: the input number is 2');\n        ELSE\n            raise_application_error(-20003,'Exception: the input number is not 1 or 2');\n        END<\/span> IF<\/span>;\n    -- exception handling of the inner block<\/span>\n    EXCEPTION\n        WHEN e1 THEN \n            dbms_output.put_line('Handle exception when the input number is 1');\n    END<\/span>;\n    -- exception handling of the outer block<\/span>\n    EXCEPTION \n        WHEN e2 THEN\n            dbms_output.put_line('Handle exception when the input number is 2');\nEND<\/span>;\n\/\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
First, execute the block and enter 1 as the input number.<\/p>\n\n\n\n
Since the input is 1, the inner block raises the e1 exception. The exception-handling part of the inner block handles the e1 exception locally, therefore, the execution of the block resumes in the enclosing block.<\/p>\n\n\n\n
Second, execute the block and enter 2 as the input number.<\/p>\n\n\n\n
The inner block raises the e2 exception. Because the inner block does not have an exception handler to handle the e2 exception, PL\/SQL propagates the e2 exception to the enclosing block.<\/p>\n\n\n\n
The enclosing block has an exception handler that handles e2 exception. Therefore control passes to the host environment (SQL*Plus or SQL Developer).<\/p>\n\n\n\n
The following picture illustrates the propagation of the unhandled exception from the inner block to its enclosing block:<\/p>\n\n\n\n
Third, execute the block and enter 3 as the input number.<\/p>\n\n\n\n
In this case, both the inner block and enclosing block have no exception handler to handle the e3 exception. Therefore, the block returns an unhandled exception to the host environment.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Use raise_application_error<\/code>  procedure to raise custom, user-defined errors.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t