Using raise_application_error - user-defined exceptions, PL-SQL Programming

Assignment Help:

Using raise_application_error

The Package DBMS_STANDARD that is supplied with Oracle gives language facilities that help your application to interact with Oracle. For illustration, the procedure raise_application_error lets you issue user-defined error messages from the stored subprograms. In that way, you can report errors to your application and avoid returning unhandled exceptions. To call the raise_application_error, use the syntax shown below:

raise_application_error(error_number, message[, {TRUE | FALSE}]);

Where error_number is a negative integer in the range of -20000... -20999 and message is a character string of upto 2048 bytes long. When the optional third parameter is TRUE, then the error is placed on the stack of earlier errors. And If the parameter is FALSE (the default), the error replaces all earlier errors. The Package DBMS_ STANDARD is an extension of the package STANDARD, so you do not require qualifying the references to its contents.

An application can only call the raise_application_error from an executing stored subprogram (or method). Whenever called, raise_application_error ends the subprogram and returns a user-defined error number and message to the application. Error number and the message can be trapped such as any Oracle error.

In the example below, you call the raise_application_error if an employee's salary is missing:

CREATE PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS

curr_sal NUMBER;

BEGIN

SELECT sal INTO curr_sal FROM emp WHERE empno = emp_id;

IF curr_sal IS NULL THEN

/* Issue user-defined error message. */

raise_application_error(-20101, 'Salary is missing');

ELSE

UPDATE emp SET sal = curr_sal + amount WHERE empno = emp_id;

END IF;

END raise_salary;

The calling applications get a PL/SQL exception that can process using the error-reporting functions SQLCODE and SQLERRM in an OTHERS handler. It can also use the pragma EXCEPTION_INIT to map precise error numbers returned by the raise_application_error to exceptions of its own, as shown below:

EXEC SQL EXECUTE

/* Execute embedded PL/SQL block using host

variables my_emp_id and my_amount, which were

assigned values in the host environment. */

DECLARE

...

null_salary EXCEPTION;

/* Map error number returned by raise_application_error

to user-defined exception. */

PRAGMA EXCEPTION_INIT(null_salary, -20101);

BEGIN

...

raise_salary(:my_emp_id, :my_amount);

EXCEPTION

WHEN null_salary THEN

INSERT INTO emp_audit VALUES (:my_emp_id, ...);

...

END;

END-EXEC;

This method allows the calling application to handle error conditions in the specific exception handlers.


Related Discussions:- Using raise_application_error - user-defined exceptions

Using first and last - collection method, Using FIRST and LAST FIRST a...

Using FIRST and LAST FIRST and LAST return the first and last (minimum and maximum) index numbers in a collection. When the collection is empty, the FIRST and LAST return NULL

Product-specific packages, Product-specific Packages The Oracle and dif...

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Rowid - sql pseudocolumns, ROWID The ROWID returns the rowid (binary a...

ROWID The ROWID returns the rowid (binary address) of a row in the database table. You can use the variables of the type UROWID to store rowids in a readable format. In the il

Sql scripts, The SQL ‘CREATE TABLE' scripts for all the tables you have imp...

The SQL ‘CREATE TABLE' scripts for all the tables you have implemented. Note that your tables must correspond exactly to the ERD you have provided in 1. above, or you will lose ma

Operator precedence-pl/sql expressions , Operator Precedence The operati...

Operator Precedence The operations within an expression are completed in a particular order depending on their precedence (priority). The table shows the default order of the op

Using trim - collection method, Using TRIM This process has two forms....

Using TRIM This process has two forms. The TRIM removes an element from the end of the collection. The TRIM(n) removes the n elements from the end of the collection. For e.g.

Short-circuit evaluation-pl/sql expressions , Short-Circuit Evaluation ...

Short-Circuit Evaluation When computing a logical expression, the PL/SQL uses short-circuit evaluation. That is, the PL/SQL stops computing the expression as soon as the result

%type - cursors, %TYPE: This attribute gives the datatype of a formerly...

%TYPE: This attribute gives the datatype of a formerly declared collection, cursor variable, object, field, record, database column, or variable. Datatype: This is simply

First step at defining type sid in sql, First Step at defining type SID in ...

First Step at defining type SID in SQL CREATE TYPE SID AS ( C VARCHAR(5) ) ; Explanation: TYPE SID announces that a type named SID is being defined to the syst

Table literals - sql, Table Literals - SQL One might expect SQL to sup...

Table Literals - SQL One might expect SQL to support table literals in the manner illustrated in Example 2.2, but in fact that is not a legal SQL expression. Example: Not a

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd