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

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.

Posted Date: 10/5/2012 5:14:51 AM | Location : United States







Related Discussions:- Using raise_application_error - user-defined exceptions, Assignment Help, Ask Question on Using raise_application_error - user-defined exceptions, Get Answer, Expert's Help, Using raise_application_error - user-defined exceptions Discussions

Write discussion on Using raise_application_error - user-defined exceptions
Your posts are moderated
Related Questions
Accessing Attributes: You can refer to an attribute only by its name not by its position in the object type. To access or modify the value of an attribute, you can use the dot

Sequential Control Dissimilar to the IF and LOOP statements, the GOTO and NULL statements are not important to the PL/SQL programming. The configuration of PL/SQL is such that th

Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

Application to Export Excel Data to MSSQL Server table I am having a table available in excel format and features the subsequent: - Some text is in Arabic (e.g. UTF-8 encodin

Example of Using Aggregation on Nested Tables Example: How many students sat each exam WITH C_ER AS (SELECT CourseId, CAST (TABLE (SELECT DISTINCT StudentId, Mark FROM EXAM

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

%FOUND Until the SQL data manipulation statement is executed, the %FOUND yields NULL. Afterward, the %FOUND yields TRUE, when an INSERT, UPDATE, or DELETE statement affected o

Advantages of Subprograms The Subprograms give extensibility; that is, tailor the PL/SQL language to suit your requirements. For illustration, if you require a procedure which

Using Pragma RESTRICT_REFERENCES: The function called from the SQL statements should obey certain rules meant to control the side effects. To check for violation of the rules,