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 count, Using COUNT The COUNT returns the number of elements that...

Using COUNT The COUNT returns the number of elements that a collection presently contains. For instance, when a varray projects contains 15 elements, then the following IF con

Example of check constraints - sql, Example of Check Constraints Examp...

Example of Check Constraints Example: Workaround for when subqueries not permitted in CHECK constraints CREATE FUNCTION NO_MORE_THAN_20000_ENROLMENTS ( ) RETURNS BOOLEAN

Procedure, 1. Create a procedure called TAX_COST_SP to accomplish the tax c...

1. Create a procedure called TAX_COST_SP to accomplish the tax calculation task. Keep in mind that the state and subtotal values are inputs into the procedure and the procedure is

What is a record, What Is a Record  ? A record is a group of related...

What Is a Record  ? A record is a group of related data items that stored in the fields, each with its own name and datatype. Assume that you have different data about an em

Relational operators and logical operators, Relational Operators and Logica...

Relational Operators and Logical Operators It prepares the ground for subsequent sections in which each specific relational operator is paired with its logical counterpart, su

Parameter and keyword description - open-for statement, Parameter and Keywo...

Parameter and Keyword Description: cursor_variable_name: This identifies a cursor variable or the parameter formerly declared within the present scope. host_cursor_va

In operator-comparison operators, IN Operator The operator IN tests the ...

IN Operator The operator IN tests the set membership. This means "equal to any member of." The set may have nulls, but they are ignored. For illustration, the statement below do

Running the pl/sql wrapper, Running the PL/SQL Wrapper To run the PL/SQ...

Running the PL/SQL Wrapper To run the PL/SQL Wrapper, go through the wrap command at your operating system prompt by using the syntax as shown: wrap iname=input_file [oname=

Difference between implicit and explicit cursor, Implicit Cursor is declare...

Implicit Cursor is declared and used by the oracle environment internally. while the explicit cursor is declared and used by the external user. more over implicitly cursors are no

Select query, Query: SELECT * FROM EMPLOYEE1; Select 5 columns and ...

Query: SELECT * FROM EMPLOYEE1; Select 5 columns and all rows from one table Query: SELECT C_ID, COMPANY, BUILDING, DEPARTMENT, BRANCH FROM CONTRACT;

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