Oracle Function Throw Error
Contents |
user-defined exceptions whose names you decide. For more information, see "Defining Your Own PL/SQL oracle raise exception with message Exceptions". Syntax raise statement ::= Description of the illustration raise_statement.gif
Oracle Raise_application_error
Keyword and Parameter Description exception_name A predefined or user-defined exception. For a list of oracle predefined exceptions the predefined exceptions, see "Summary of Predefined PL/SQL Exceptions". Usage Notes PL/SQL blocks and subprograms should RAISE an exception only when an error oracle raise no_data_found makes it impractical to continue processing. You can code a RAISE statement for a given exception anywhere within the scope of that exception. When an exception is raised, if PL/SQL cannot find a handler for it in the current block, the exception propagates to successive enclosing blocks, until
Exception Part Can Be Defined Twice In Same Block
a handler is found or there are no more blocks to search. If no handler is found, PL/SQL returns an unhandled exception error to the host environment. In an exception handler, you can omit the exception name in a RAISE statement, which raises the current exception again. This technique allows you to take some initial corrective action (perhaps just logging the problem), then pass control to another handler that does more extensive correction. When an exception is reraised, the first block searched is the enclosing block, not the current block. Examples For examples, see the following: Example 1-12, "Creating a Stored Subprogram" Example 9-3, "Creating the emp_admin Package" Example 10-3, "Scope of PL/SQL Exceptions" Example 10-9, "Reraising a PL/SQL Exception" Related Topics "Exception Definition" Scripting on this page enhances content navigation, but does not change the content in any way.
here for a quick overview of the site Help Center Detailed answers to any questions you might have pl sql exception handling examples Meta Discuss the workings and policies of this site About Us Learn functions for error trapping are contained in which section of a pl/sql block more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us
Pl/sql Raises An Exception In Which Two Of The Following Cases
Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/raise_statement.htm you, helping each other. Join them; it only takes a minute: Sign up Oracle PL/SQL - Raise User-Defined Exception With Custom SQLERRM up vote 45 down vote favorite 21 Is it possible to create user-defined exceptions and be able to change the SQLERRM? For example: DECLARE ex_custom EXCEPTION; BEGIN RAISE ex_custom; EXCEPTION WHEN ex_custom THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); http://stackoverflow.com/questions/6020450/oracle-pl-sql-raise-user-defined-exception-with-custom-sqlerrm END; / The output is "User-Defined Exception". Is it possible to change that message? EDIT: Here is some more detail. I hope this one illustrates what I'm trying to do better. DECLARE l_table_status VARCHAR2(8); l_index_status VARCHAR2(8); l_table_name VARCHAR2(30) := 'TEST'; l_index_name VARCHAR2(30) := 'IDX_TEST'; ex_no_metadata EXCEPTION; BEGIN BEGIN SELECT STATUS INTO l_table_status FROM USER_TABLES WHERE TABLE_NAME = l_table_name; EXCEPTION WHEN NO_DATA_FOUND THEN -- raise exception here with message saying -- "Table metadata does not exist." RAISE ex_no_metadata; END; BEGIN SELECT STATUS INTO l_index_status FROM USER_INDEXES WHERE INDEX_NAME = l_index_name; EXCEPTION WHEN NO_DATA_FOUND THEN -- raise exception here with message saying -- "Index metadata does not exist." RAISE ex_no_metadata; END; EXCEPTION WHEN ex_no_metadata THEN DBMS_OUTPUT.PUT_LINE('Exception will be handled by handle_no_metadata_exception(SQLERRM) procedure here.'); DBMS_OUTPUT.PUT_LINE(SQLERRM); END; / In reality, there are dozens of those sub-blocks. I'm wondering if there's a way to have a single user-defined exception for each of those sub-blocks to raise, but have it give a different message, instead of creating a separate user-defined exception for each sub-
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta http://stackoverflow.com/questions/22234004/is-it-best-practice-for-functions-to-return-after-raising-an-error-in-pl-sql Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping oracle raise each other. Join them; it only takes a minute: Sign up Is it best practice for functions to return after raising an error in PL/SQL up vote 5 down vote favorite 1 I have a bunch of functions in packages that first check the validity of the work being requested and throw exceptions if not. Example: package oracle function throw body foo as function implode ( i_foo_id number ) return implode_id as not_implodable exception; implode_id number; begin if not is_implodable(i_foo_id) then raise not_implodable; end if; //Implode logic here. return implode_id; exception when not_implodable then raise_application_error(-20005, 'Imploding is not possible on this foo.'); end implode; end foo; I have turned on warning reporting and get something like this when I compile. Warning(67,3): PLW-05005: subprogram IMPLODE returns without value at line 14. If I put a return statement after the raise_application_error then the warning goes away. Since raising the error exits the call stack, is there any good reason to return null anyways? oracle plsql share|improve this question edited Mar 11 '14 at 14:01 asked Mar 6 '14 at 19:23 David Baucum 819816 add a comment| 3 Answers 3 active oldest votes up vote 2 down vote accepted No. It is not a best practice for functions to return after raising and error in PL/SQL. It is possible and sometimes even recommended to add code just to shut up the compiler, like: exceptio