Oracle 11g Pl Sql Raise Application Error
Contents |
program to continue to operate in the presence of errors. Topics: Overview of PL/SQL Run-Time Error Handling Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions Advantages of
Raise Application Error Oracle Example
PL/SQL Exceptions Predefined PL/SQL Exceptions Defining Your Own PL/SQL Exceptions How PL/SQL Exceptions pl sql exception handling examples Are Raised How PL/SQL Exceptions Propagate Reraising a PL/SQL Exception Handling Raised PL/SQL Exceptions Overview of PL/SQL Compile-Time Warnings
User Defined Exception In Pl Sql
Overview of PL/SQL Run-Time 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 pl sql exception handling continue loop defined exceptions are ORA-22056 (value string is divided by zero) and ORA-27102 (out of memory). Some common internal exceptions have predefined names, 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 exception handling in oracle 11g example 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 managing errors when using BULK COLLECT, see Handling FORALL Exceptions (%BULK_EXCEPTIONS Attribute). Example 11-1 calculates a price-to-earnings ratio for a company. If the company has zero earnings, the division operation raises the predefined exception ZERO_DIVIDE, the execution of the block is interrupted, and control is transferred to the exception handlers. The optional OTHERS handler catches all exceptions that the block does not name specifically. Example 11-1 Run-Time Error Handling DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN -- Calculation mig
errors. The latter are called exceptions. Note: The language of warning and error messages depends on the NLS_LANGUAGE parameter. For information about this parameter, see Oracle Database Globalization
Pl Sql Exception When Others
Support Guide. Topics Compile-Time Warnings Overview of Exception Handling Internally Defined Exceptions Predefined Exceptions functions for error trapping are contained in which section of a pl/sql block User-Defined Exceptions Redeclared Predefined Exceptions Raising Exceptions Explicitly Exception Propagation Unhandled Exceptions Error Code and Error Message Retrieval Continuing Execution After
Pl Sql Exception Handling Best Practices
Handling Exceptions Retrying Transactions After Handling Exceptions See Also: "Exception Handling in Triggers" "Handling FORALL Exceptions After FORALL Statement Completes" Tip: If you have problems creating or running PL/SQL code, check the Oracle Database https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm trace files. The USER_DUMP_DEST initialization parameter specifies the current location of the trace files. You can find the value of this parameter by issuing SHOW PARAMETER USER_DUMP_DEST. For more information about trace files, see Oracle Database Performance Tuning Guide. Compile-Time Warnings While compiling stored PL/SQL units, the PL/SQL compiler generates warnings for conditions that are not serious enough to cause errors and prevent compilation—for example, using a deprecated https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm PL/SQL feature. To see warnings (and errors) generated during compilation, either query the static data dictionary view *_ERRORS (described in Oracle Database Reference) or, in the SQL*Plus environment, use the command SHOW ERRORS. The message code of a PL/SQL warning has the form PLW-nnnnn. For the message codes of all PL/SQL warnings, see Oracle Database Error Messages. Table 11-1 summarizes the categories of warnings. Table 11-1 Compile-Time Warning Categories Category Description Example SEVERE Condition might cause unexpected action or wrong results. Aliasing problems with parameters PERFORMANCE Condition might cause performance problems. Passing a VARCHAR2 value to a NUMBER column in an INSERT statement INFORMATIONAL Condition does not affect performance or correctness, but you might want to change it to make the code more maintainable. Code that can never run By setting the compilation parameter PLSQL_WARNINGS, you can: Enable and disable all warnings, one or more categories of warnings, or specific warnings Treat specific warnings as errors (so that those conditions must be corrected before you can compile the PL/SQL unit) You can set the value of PLSQL_WARNINGS for: Your Oracle database instance Use the ALTER SYSTEM statement, described in Oracle Database SQL Language Reference. Your session Use the ALTER SESSION statement, described in Or
FunctionsRegular Expressions FunctionsStatistical FunctionsLinear Regression FunctionsPL SQL Data TypesPL SQL StatementsPL SQL OperatorsPL SQL ProgrammingCursorCollectionsFunction Procedure PackagesTriggerSQL PLUS Session EnvironmentSystem Tables Data DictionarySystem PackagesObject OrientedXMLLarge ObjectsTransactionUser PrivilegeA complete example using RAISE_APPLICATION_ERROR : raise_application_error«PL http://www.java2s.com/Tutorial/Oracle/0480__PL-SQL-Programming/AcompleteexampleusingRAISEAPPLICATIONERROR.htm SQL Programming«Oracle PL/SQL TutorialOracle PL/SQL TutorialPL SQL Programmingraise_application_errorSQL> SQL> SQL> create table 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 ); Table created. SQL> insert into company values(1,1001,'A Inc.','Long Name A Inc.'); 1 row created. SQL> insert pl sql 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 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 pl sql exception 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, 25 company_long_name =p_company_long_name 26 WHERE product_id =p_product_id 27 AND company_id =p_company_id; 28 IF SQL%NOTFOUND THEN 29 RAISE_APPLICATION_ERROR(-20002,'Organization '||TO_CHAR(p_company_id)||' does not exist.'); 30 END IF; 31 END; 32 ELSIF (p_flag_in ='D')THEN 33 BEGIN 34 DELETE company 35 WHERE product_id =p_product_id 36 AND company_id =p_company_id; 37 IF SQL%NOTFOUND THEN 38 RAISE_APPLICATION_ERROR