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

Open-for statement, OPEN-FOR Statement The OPEN-FOR statements execute ...

OPEN-FOR Statement The OPEN-FOR statements execute the multi-row query related with a cursor variable. It also allocates the resources used by the Oracle to process the query a

Goto statement - syntax, GOTO Statement   The GOTO statement branches ...

GOTO Statement   The GOTO statement branches categorically to a block label or statement label. The label should be exclusive within its scope and should precede a PL/SQL bloc

Semijoin and composition - sql, Semijoin and Composition - SQL For sem...

Semijoin and Composition - SQL For semijoin, the dyadic relational operator MATCHING, defined thus: r1 MATCHING r2, where r1 and r2 are relations such that r1 JOIN r2 is de

Managing cursors, Managing Cursors The PL/SQL uses 2 types of cursors: ...

Managing Cursors The PL/SQL uses 2 types of cursors: implicit and explicit. The PL/SQL declares a cursor implicitly for all the SQL data manipulation statements, including th

Nested tables versus index-by tables, Nested Tables versus Index-by Tables ...

Nested Tables versus Index-by Tables The Index-by tables and nested tables are just similar. For e.g.  They have similar structure and their individual elements are accessed in

Dbms, DBMS: The answer to this question is of course given in of the t...

DBMS: The answer to this question is of course given in of the theory book. This book is concerned with SQL DBMSs and SQL databases in particular. Soon we will be looking a

Aggregate assignment-declarations in sql, Aggregate Assignment The %ROWT...

Aggregate Assignment The %ROWTYPE declaration cannot include an initialization clause. Though, there are two ways to assign values to all fields in a record at once. At First, t

Row counterparts of table operators - sql, Row Counterparts of Table Operat...

Row Counterparts of Table Operators SQL does not have counterparts tuple rename, tuple projection, tuple extension, tuple join and tuple compose. To obtain the same effects as

Update the Status of an Ordere, Create a procedure named STATUS_SHIP_SP tha...

Create a procedure named STATUS_SHIP_SP that allows a company to employee in the Shipping Department to update the status of an order to add shipping information. The BB_BASKETSTAT

Using operator deref - manipulating objects in pl sql, Using Operator DEREF...

Using Operator DEREF: You cannot navigate through refs within the PL/SQL procedural statements. Rather than, you should use the operator DEREF in the SQL statement. The DEREF

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