Raise Application Error In Oracle
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
Oracle Raise
RAISE_APPLICATION_ERROR tips Oracle Database Tips by Burleson difference between raise and raise_application_error in oracle Consulting March 3, 2015 Question: What does the PL/SQL raise_application_error code do? Answer: The raise_application_error is actually a procedure defined by raise_application_error(-20001 Oracle that allows the developer to raise an exception and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors. Error numbers
Raise_application_error Parameters
are defined between -20,000 and -20,999. Oracle provides the raise_application_error procedure to allow you to raise custom error numbers within your applications. You can generate errors and their associated text starting with -20000 and proceeding through -20999 (a grand total of 1,000 error numbers that you can use). Below we illustrate the use of the raise_application_error procedure. Using the raise_application_error procedure: DECLARE Balance integer := 24; BEGIN IF (nBalance
Raise Application Error In Oracle Triggers
<= 100) THEN Raise_Application_Error (-20343, 'The balance is too low.');END IF;END; In this example, error number -20343 is raised if the value of nBalance isn't greater than 100, yielding a message that looks like this: ORA-20343: The balance is too low. All other numbers belong to Oracle for its own errors. The message can be anything that will fit in a varchar2(2000). The final parameter passed to the procedure is a Boolean(true/false) that tells the procedure to add this error to the error stack or replace all errors in the stack with this error. Passing the value of 'True' adds the error to the current stack, while the default is 'False'. SQL> create or replace procedure test_var2 (n_test IN number := 0,3 n_result OUT number)4 as5 begin 6 if n_test > 100 then7 raise_application_error(-20010,'Number Too Large');8 end if;9 n_result := n_test;10 end; 11 / Procedure created. SQL> declare2 n_numb number := &Number;3 n_2 number := 0;4 begin5 test_var(n_numb, n_2);6 dbms_output.put_line(n_2);7 end;8 /Enter value for number: 5old 2: n_numb number := &Number;new 2: n_numb number := 5;5 PL/SQL procedure successfully completed. SQL> /Enter value for number: 105old 2: n_numb number := &Number;new 2: n_numb number := 105; declare*ERROR at line 1:ORA-20010: Number Too LargeORA-06512: at "PUBS.TES
program to continue to operate in the presence of errors. Topics: Overview of PL/SQL raise_application_error sqlerrm Run-Time Error Handling Guidelines for Avoiding and Handling PL/SQL Errors
Explain Different Looping Statement In Pl/sql
and Exceptions Advantages of PL/SQL Exceptions Predefined PL/SQL Exceptions Defining Your Own PL/SQL Exceptions How difference between pragma exception_init and raise_application_error PL/SQL Exceptions Are Raised How PL/SQL Exceptions Propagate Reraising a PL/SQL Exception Handling Raised PL/SQL Exceptions Overview of PL/SQL Compile-Time Warnings Overview of PL/SQL Run-Time http://www.dba-oracle.com/t_raise_application_error.htm Error Handling In PL/SQL, an error condition is called an exception. An exception can be either internally defined (by the run-time system) or user-defined. Examples of internally defined exceptions are ORA-22056 (value string is divided by zero) and ORA-27102 (out of memory). Some common internal exceptions have predefined names, https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names. You can define your own exceptions in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. User-defined exceptions must be given names. When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements or invocations of the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR. To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment. For information about ma
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books http://www.dba-oracle.com/t_raise_application_error.htm Oracle Scripts Ion Excel-DB Don Burleson Blog
RAISE_APPLICATION_ERROR tips Oracle Database Tips by Burleson Consulting March http://www.toadworld.com/platforms/oracle/b/weblog/archive/2010/07/14/raise-vs-raise-application-error 3, 2015 Question: What does the PL/SQL raise_application_error code do? Answer: The raise_application_error is actually a procedure defined by Oracle that allows the developer to raise an exception error in and associate an error number and message with the procedure. This allows the application to raise application errors rather than just Oracle errors. Error numbers are defined between -20,000 and -20,999. Oracle provides the raise_application_error procedure to allow you to raise custom error numbers within your applications. You can generate errors and their associated text starting error in oracle with -20000 and proceeding through -20999 (a grand total of 1,000 error numbers that you can use). Below we illustrate the use of the raise_application_error procedure. Using the raise_application_error procedure: DECLARE Balance integer := 24; BEGIN IF (nBalance <= 100) THEN Raise_Application_Error (-20343, 'The balance is too low.');END IF;END; In this example, error number -20343 is raised if the value of nBalance isn't greater than 100, yielding a message that looks like this: ORA-20343: The balance is too low. All other numbers belong to Oracle for its own errors. The message can be anything that will fit in a varchar2(2000). The final parameter passed to the procedure is a Boolean(true/false) that tells the procedure to add this error to the error stack or replace all errors in the stack with this error. Passing the value of 'True' adds the error to the current stack, while the default is 'False'. SQL> create or replace procedure test_var2 (n_test IN number := 0,3 n_result OUT number)4 as5 begin 6 iSQL Server PRODUCTSDiscussion & Resources Benchmark Factory Code Tester for Oracle SharePlex SQL Navigator SQL Optimizer Spotlight Stat Toad Intelligence Central Toad Data Modeler Toad Data Point Toad Extension for Eclipse Toad for Hadoop Toad for IBM DB2 Toad for Oracle Toad for MySQL Toad for SQL Server Toad for SAP Solutions Toad MAC Edition Home » Platforms » Oracle » Oracle Blog » RAISE vs RAISE_APPLICATION_ERROR? RAISE vs RAISE_APPLICATION_ERROR? Oracle Community Join Overview Forum Blog Wiki Members Blog Options Print Comment RSS Feed Tweet Related Posts "Our" Error Codes : -20999 to -20000 by Steven Feuerstein on 3 Feb 2012 0 comments RAISE_APPLICATION_ERROR latest revision over 3 years ago by Steve Hilker Who needs comments? My code is self-documenting! AKA: Comment tersely with value-added information. by Steven Feuerstein on 28 Nov 2012 0 comments View More RAISE vs RAISE_APPLICATION_ERROR? Follow Steven Feuerstein / 7.14.2010 at 9:01pm A PL/SQL developer just sent me the following question: "I can't understand the difference between RAISE_APPLICATION_ERROR and RAISE. Why would I use one vs. the other?" Instead of answering via an email to just one developer, I thought I might share my answer with everyone. The RAISE_APPLICATION_ERROR built-in (defined in the DBMS_STANDARD package) should be used for just a single scenario: you need to communicate an application-specific error back to the user. Suppose, for example, I have a rule for the employees table that the minimum salary allowed is $100,000 (ah, wouldn't that be nice?). I want to enforce that rule through a database trigger: TRIGGER employees_minsal_tr BEFORE INSERT OR UPDATE ON employees FOR EACH ROWBEGIN IF :new.salary < 100000 THEN /* communicate error */ NULL; END IF;END; I can stop the DML from completing by issuing a RAISE statement, such as: RAISE PROGRAM_ERROR; But I would not be able to communicate back to the user what the actual problem was. If, on the other hand, I use RAISE_APPLICATION_ERROR, I can specify the error number (of little interest to my users, but a good "identifier" for support) and, more importantly, the error message, as in: TRIGGER employees_minsal_tr BEFORE INSERT OR UPDATE ON employees FOR EACH ROWBEGIN IF :new.salary < 1000000 THEN RAISE_APPLICATION_ERRO