, , ,

Packages in PL SQL

A PL/SQL package is a single program unit that contains one or more procedures and/or functions, as well as various other PL/SQL constructs such as cursors, variables, constants, and exceptions. Packages bring these various constructs together in a single program unit.
Most applications include several Procedural Language/Structured Query Language (PL/SQL) procedures and functions that are logically related together. These various procedures and functions could be left as stand-alone individual procedures and functions, stored in the database. However, they can also be collected in a package, where they can be more easily organized and where you will find certain performance improvements as well as access control and various other benefits.

A package is a collection of PL/SQL program constructs, including variables, constants, cursors, user-defined exceptions, and PL/SQL procedures and functions, as well as PL/SQL-declared types. A package groups all of these constructs under one name. More than that, the package owns these constructs, and in so doing, affords them powers and performance benefits that would not otherwise exist if these constructs were not packaged together.

/by
, , ,

Error Reporting Functions

In an exception handler, you can use the built-in functions SQLCODE and SQLERRM to find out which error occurred and to get the associated error message.
1. For internal exceptions, SQLCODE returns the number of the Oracle error. The number that SQLCODE returns is negative unless the Oracle error is no data found, in which case SQLCODE returns +100. SQLERRM returns the corresponding error message. The message begins with the Oracle error code.
2. For user-defined exceptions, SQLCODE returns +1 and SQLERRM returns the message User-Defined Exception unless you used the pragma EXCEPTION_INIT to associate the exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM returns the corresponding error message.
The maximum length of an Oracle error message is 512 characters including the error code, nested messages, and message inserts such as table and column names.
If no exception has been raised, SQLCODE returns zero and SQLERRM returns the message ORA-0000: normal, successful completion.

Passing  an error number
You can pass an error number to SQLERRM, in which case SQLERRM returns the message associated with that error number. Make sure you pass negative error numbers to SQLERRM. In the following example, you pass positive numbers and so get unwanted results:
DECLARE
…
err_msg VARCHAR2(100);
BEGIN
/* Get all Oracle error messages. */
FOR err_num IN 1..9999 LOOP
err_msg := SQLERRM(err_num); — wrong; should be -err_num
INSERT INTO errors VALUES (err_msg);
END LOOP;
END;
Passing a positive number to SQLERRM always returns the message user-defined exception unless you pass +100, in which case SQLERRM returns the message no data found. Passing a zero to SQLERRM always returns the message normal, successful completion.
SQLCODE or SQLERRM in SQL statements
You cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must assign their values to local variables, then use the variables in the SQL statement, as shown in the following example:
DECLARE
err_num NUMBER;
err_msg VARCHAR2(100);
BEGIN
…
EXCEPTION
…
WHEN OTHERS THEN
err_num := SQLCODE;
err_msg := SUBSTR(SQLERRM, 1, 100);
INSERT INTO errors VALUES (err_num, err_msg);
END;
The string function SUBSTR ensures that a VALUE_ERROR exception (for truncation) is not raised when you assign the value of SQLERRM to err_msg. The functions SQLCODE and SQLERRM are especially useful in the OTHERS exception handler
because they tell you which internal exception was raised.

/by
, , ,

NonPredefined Error

To handle unnamed internal exceptions, you must use the OTHERS handler or the pragma EXCEPTION_INIT. A pragma is a compiler directive, which can be thought of as a parenthetical remark to the compiler. Pragmas (also called pseudoinstructions)
are processed at compile time, not at run time.
In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle error number. That allows you to refer to any internal exception by name and to write a specific handler for it. You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax
PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number);
where exception_name is the name of a previously declared exception. The pragma must appear somewhere after the exception declaration in the same declarative section, as shown in the following example:

/by
, , ,

Predefined Exceptions

An internal exception is raised implicitly whenever your PL/SQL program violates an Oracle rule or exceeds a system-dependent limit. Every Oracle error has a number, but exceptions must be handled by name. So, PL/SQL predefines some common Oracle errors as exceptions. For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows.
To handle other Oracle errors, you can use the OTHERS handler. The functions SQLCODE and SQLERRM are especially useful in the OTHERS handler because they return the Oracle error code and message text. Alternatively, you can use the pragma EXCEPTION_INIT to associate exception names with Oracle error codes.
PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. So, you need not declare them yourself. You can write handlers for predefined exceptions using the names shown in the list below. Also shown are the corresponding Oracle error codes and SQLCODE return values.

/by