Raise And Raise Application Error
Contents |
� Last updated: July 05, 2005 - 9:47 am UTC Category: Developer � Version: 8.1.7 Whilst you are here, check out some content from the AskTom team: Is it all about End Users or End Boredoms? Latest Followup You Asked Tom What is the difference between pragma exception_init
Raise Application Error Oracle Example
, and raise application error( which is a subprogram from dbms_standard package). In what situations do pragma exception_init and raise application error you use a Raise application_error subprogram. Thanks and we said... pragma exception init turns an Oracle Error into a named exception. If a database
Oracle Raise
operation raises an ORA-00054 "resource busy", you would have to code: .... exception when others then if (sqlcode=-54) then .... deal with it. else RAISE; end if; if you pragma exception init'ed this error to some named exception RESOURCE_BUSY raise_application_error(-20001 , you can code: .... pragma exception_init( resource_busy, -54 ); begin .... exception when resource_busy then .... deal with it. end; much cleaner (i hate when others -- should be outlawed). Raise_application_error is used to RAISE an error - exception_init is used to deal with errors (i guess you could say they are opposites in a way). I use raise_application_error when I want to setup a custom error message to be returned to a client application raise_application_error( -20001, 'You cannot raise_application_error error number range do that!' ); If I just fling a named exception, the client gets the truly usless error message "unhandled user defined exception". If I use raise_application_error -- the client gets whatever I send back. Reviews Write a Review May 14, 2001 - 4:31 am UTC Reviewer: Helena Markova from Bratislava, Slovakia Which table stores the mapping of user defined exception numbers January 28, 2003 - 1:23 pm UTC Reviewer: ss from India When we use pragma exception_init (ABCDXYZ, -2000n ) which table stores this mapping in the database? Thanks Followup January 28, 2003 - 3:08 pm UTC none, well, its in pcode stored in the diana netlibs in the data dictionary but not in any sort of format you and I could get. It is part of the code, it isn't stored anywhere relationaly ORA-00054 in another session with PRAGMA AUTONOMOUS TRANSACTION November 14, 2003 - 1:52 pm UTC Reviewer: Vera from D.C. Hi, Tom, As always, thank you for everything you do for us. I hate to bother you, but I did not find an answer anywhere yet. In your book you listed some caveats, but in my case it is probably something different. I created a simple test package with 3 procedures. One of them PRAGMA AUTONOMOUS_TRANSACTION. When I run the package in one session everything is fine. After it completed, I go to another session and try to execute it and I am getting
SQL 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
Raise Application Error In Sql Server
for MySQL Toad for SQL Server Toad for SAP Solutions Toad MAC Edition raise_application_error sqlerrm Home » Platforms » Oracle » Oracle Blog » RAISE vs RAISE_APPLICATION_ERROR? RAISE vs RAISE_APPLICATION_ERROR? Oracle Community Join Overview Forum Blog Wiki
Raise_application_error Parameters
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 https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:636489087908 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 http://www.toadworld.com/platforms/oracle/b/weblog/archive/2010/07/14/raise-vs-raise-application-error 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_ERROR (-20000, 'Salary of '|| :new.salary || ' is too low. It must be at least $100,000.'); END IF;END; And that, dear reader, is the motivation for using RAISE_APPLICATION_ERROR: the ability to communicate a custom, application-specific error message to your users.
FunctionsRegular Expressions FunctionsStatistical FunctionsLinear Regression FunctionsPL SQL Data TypesPL SQL StatementsPL SQL OperatorsPL SQL ProgrammingCursorCollectionsFunction Procedure PackagesTriggerSQL PLUS Session EnvironmentSystem Tables Data DictionarySystem PackagesObject http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/AcompleteexampleusingRAISEAPPLICATIONERROR.htm OrientedXMLLarge ObjectsTransactionUser PrivilegeA complete example using RAISE_APPLICATION_ERROR : raise_application_error«PL SQL Programming«Oracle PL/SQL TutorialOracle PL/SQL TutorialPL SQL Programmingraise_application_errorSQL> SQL> SQL> create table http://plsql-tutorial.com/plsql-exception-handling.htm company( 2 product_id number(4) not null, 3 company_id NUMBER(8) not null, 4 company_short_name varchar2(30) not null, 5 company_long_name varchar2(60) 6 ); raise application Table created. SQL> insert into company values(1,1001,'A Inc.','Long Name A Inc.'); 1 row created. SQL> insert into company values(1,1002,'B Inc.','Long Name B Inc.'); 1 row created. SQL> insert into company values(1,1003,'C Inc.','Long Name C Inc.'); 1 row created. SQL> insert into raise application error company values(2,1004,'D Inc.','Long Name D Inc.'); 1 row created. SQL> insert into company values(2,1005,'E Inc.','Long Name E Inc.'); 1 row created. SQL> insert into company values(2,1006,'F Inc.','Long Name F Inc.'); 1 row created. SQL> SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE org_proc 2 (p_flag_in VARCHAR2, 3 p_product_id NUMBER, 4 p_company_id NUMBER, 5 p_company_short_name VARCHAR2, 6 p_company_long_name VARCHAR2) 7 IS 8 v_error_code NUMBER; 9 BEGIN 10 IF (p_flag_in ='I')THEN 11 BEGIN 12 INSERT INTO company VALUES(p_product_id,p_company_id,p_company_short_name,p_company_long_name); 13 EXCEPTION WHEN OTHERS THEN 14 v_error_code :=SQLCODE; 15 IF v_error_code =-1 THEN 16 RAISE_APPLICATION_ERROR(-20000,'Organization '||TO_CHAR(p_company_id)||' already exists.'); 17 ELSIF v_error_code =-2291 THEN 18 RAISE_APPLICATION_ERROR(-20001,'Invalid Hierarchy Code '||TO_CHAR(p_product_id)||' specified.'); 19 END IF; 20 END; 21 ELSIF (p_flag_in ='C')THEN 22 BEGIN 23 UPDATE company 24 set company_short_name =p_company_short_name,
| PL/SQL Exception Handling Exception Handling In this section we will discuss about the following, 1) What is Exception Handling. 2) Structure of Exception Handling. 3) Types of Exception Handling. 1) What is Exception Handling? PL/SQL provides a feature to handle the Exceptions which occur in a PL/SQL Block known as exception Handling. Using Exception Handling we can test the code and avoid it from exiting abruptly. When an exception occurs a messages which explains its cause is recieved. PL/SQL Exception message consists of three parts. 1) Type of Exception 2) An Error Code 3) A message By Handling the exceptions we can ensure a PL/SQL block does not exit abruptly. 2) Structure of Exception Handling. General Syntax for coding the exception section DECLARE Declaration section BEGIN Exception section EXCEPTION WHEN ex_name1 THEN -Error handling statements WHEN ex_name2 THEN -Error handling statements WHEN Others THEN -Error handling statements END; General PL/SQL statments can be used in the Exception Block. When an exception is raised, Oracle searches for an appropriate exception handler in the exception section. For example in the above example, if the error raised is 'ex_name1 ', then the error is handled according to the statements under it. Since, it is not possible to determine all the possible runtime errors during testing fo the code, the 'WHEN Others' exception is used to manage the exceptions that are not explicitly handled. Only one exception can be raised in a Block and the control does not return to the Execution Section after the error is handled. If there are nested PL/SQL blocks like this. DELCARE Declaration section BEGIN DECLARE Declaration section BEGIN Execution section EXCEPTION Exception section END; EXCEPTION Exception section END; In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block. If none of the blocks handle the exception the program ends abruptly with an error. 3) Types of Exception. There are 3 types of Exceptions. a) Named System Exceptions b) Unnamed System Exceptions c) User-defined Exceptions a) Named System Exceptions Syst