, , ,

Create, Alter and Drop – Packages

The statements used to create, alter, and drop packages are rather straightforward. However, this process is a little more involved than merely creating a procedure or function. The first point to understand is that a package consists of two parts: the package specification and the package body. The two parts are created separately. Any package must have a specification.
A package may optionally include a package body but is not necessarily required to. The requirement for a package to have a body will be determined by what you declare in a package specification; you may simply declare a package specification and no body. However, most packages often include both the package specification and the package body.
You can declare a package specification without the package body and successfully store it in the database as a valid object. Furthermore, with only a package specification, it’s possible to create other PL/SQL programs that call on the constructs of your package, even procedures or functions, whose code isn’t written yet—the actual code can only be defined in the package body. However, the existence of the package specification enables other outside PL/SQL program units to reference the constructs of this package.
It’s recommended that you create the package specification first, before the package body. The package specification, as we have seen, will successfully store, compile, and support the successful compilation of outside program units. A package body, on the other hand, cannot be compiled successfully without a corresponding package specification. However, the package body can be submitted and stored in the data dictionary without a package specification. The package body will simply be flagged with a status of INVALID in the USER_OBJECTS data dictionary view. After the package specification is successfully created, you need to either issue the ALTER PACKAGE … COMPILE statement, or simply reference a packaged construct and let the database automatically compile the package at that time.

Creating a Package Specification
The following is an example of a statement that creates a stored PL/SQL package specification:
CREATE OR REPLACE PACKAGE pack_booking AS
  c_tax_rate NUMBER(3,2) := 0.05;
  CURSOR cur_cruises IS
    SELECT CRUISE_ID, CRUISE_NAME
    FROM   CRUISES;
  rec_cruises cur_cruises%ROWTYPE;
  FUNCTION func_get_start_date
    (p_cruise_id IN CRUISES.CRUISE_ID%TYPE)
  RETURN DATE;
END pack_booking;
The syntax of the statement is the reserved word CREATE, followed by the optional OR REPLACE words, the reserved word PACKAGE, the name you choose for the package, and the reserved word AS. (The word IS is also accepted here.) Next is a series of declared constructs. Eventually, the closing END is included, followed by the package name, optionally repeated for clarity, and then the semicolon.
This package specification declares a constant c_tax_rate, a cursor cur_cruises, a record variable rec_cruises, and the function func_get_start_date. Notice that the function’s actual code isn’t included here, only the function header.
The package specification is the part of a package that declares the constructs of the package. These declared constructs may include any of the following:

  • Variables and/or constants
  • Compound datatypes, such as PL/SQL tables and TYPEs
  • Cursors
  • Exceptions
  • Procedure headers and function headers
  • Comments

The package specification contains no other code. In other words, the actual source code of procedures and functions is never included in the package specification. The specification merely includes enough information to enable anyone who wants to use these constructs to understand their names, parameters, and their datatypes, and in the case of functions, their return datatypes, so that developers who want to write calls to these constructs may do so. In other words, if any developer wants to create new programs that invoke your package constructs, all the developer needs to see is the package specification. That’s enough information to create programs that employ the procedures and functions of your package. The developer does not need to have full access to the source code itself, provided that he or she understands the intent of the program unit.
Once the package specification has been successfully stored in the database, it will be given a status of VALID, even if the associated package body, containing the actual source code of any and all functions and/or procedures, has yet to be stored in the database.
Creating a Package Body
The following is a sample of a package body that correlates to the package specification we saw earlier:
CREATE OR REPLACE PACKAGE BODY pack_booking AS
  FUNCTION func_get_start_date
    (p_cruise_id IN CRUISES.CRUISE_ID%TYPE)
  RETURN DATE
  IS
     v_start_date CRUISES.START_DATE%TYPE;
  BEGIN
     SELECT START_DATE
     INTO   v_start_date
     FROM   CRUISES
     WHERE  CRUISE_ID = p_cruise_id;
     RETURN v_start_date;
  END func_get_start_date;
END pack_booking;
This package body defines the source code of the function func_get_start_date. Notice that the function header is completely represented here, even though it was already declared completely in the package specification. Also, notice that there are no provisions for the other declared constructs in the package specification. Only the functions and/or procedures that were declared in the package specification need to be defined in the package body. The package specification handles the full declaration of the other public constructs, such as variables, constants, cursors, types, and exceptions.
The package body is only required if the package specification declares any procedures and/or functions, or in some cases of declared cursors, depending on the cursor syntax that is used. The package body contains the complete source code of those declared procedures and/or functions, including the headers that were declared in the specification.
The package body can also include privately defined procedures and/or functions. These are program units that are recognized and callable only from within the package itself and are not callable from outside the package. They are not declared in the package specification, but are defined in the package body.
Altering a Package
Packages, like procedures and functions, should be recompiled with the ALTER command if their referenced constructs are changed for any reason. This includes any referenced database objects, such as tables, views, snapshots, synonyms, and other PL/SQL packages, procedures, and functions.
The syntax to recompile a package with the ALTER statement is
ALTER PACKAGE package_name COMPILE;
This statement will attempt to recompile the package specification and the package body.
The syntax to recompile just the package body is
ALTER PACKAGE package_name COMPILE BODY;
Note that the package is listed in the data dictionary with two records: one record for the PACKAGE and another for the PACKAGE BODY. Both have their individual status assignments. The PACKAGE, meaning the package specification, can be VALID, while the PACKAGE BODY is INVALID. If this is the case, then an ALTER PACKAGE package_name COMPILE statement will attempt to restore the entire package, including the body, to a status of VALID.
If a change is made to the package body and it is recompiled, then the package specification does not demand that the package be recompiled. Even if the recreated package body results in a change that is inconsistent with the package specification, the package specification will still show a status of VALID in the data dictionary (assuming it was VALID to begin with), and the package body will be flagged with a status of INVALID
Dropping a Package
You have two options when dropping a package. The following statement will remove the package body reservations from the database:
DROP PACKAGE BODY RESERVATIONS;
This statement will remove the package body, but will leave the package specification in the database. Furthermore, the package specification for reservations will still be VALID.
The following statement will remove the entire package:
DROP PACKAGE RESERVATIONS;
The result of issuing this statement to the database will be the complete removal of both the package specification and the package body from the database.
Dropping a package will cause any other program units that reference the package to be flagged with a status of INVALI
Public versus Private Constructs
Constructs that are declared in the package specification are considered public constructs. However, a package body can also include constructs that aren’t declared in the package specification. These are considered private constructs, which can be referenced from anywhere within its own package body but cannot be called from anywhere outside the particular package body. Furthermore, any developers with privileges to use the constructs of the package do not necessarily have to see the package body, which means that they do not necessarily know of the existence of the private constructs contained within the package body.
Global Constructs
Package constructs, such as variables, constants, cursors, types, and user-defined exceptions, are global to the user session that references them. Note that this dynamic is irrelevant for packaged procedures and packaged functions, but applies to all other packaged constructs. This is true for both public and private constructs in the package. In other words, the values for these constructs will be retained across multiple invocations within the user session.
For example, if an anonymous PL/SQL block references a public packaged variable and changes its value, the changed value can be identified by another PL/SQL block that executes afterwards. Neither block declares the variable because it’s declared as part of the package.
The user cannot directly access any private constructs, such as a variable, but imagine that a user invokes a packaged procedure, for example, that references its own private variable value and changes that value. If the user re-invokes that packaged procedure again within the same user session, the changed value will be recognized by the packaged procedure.
The value will be retained as long as the user session is still active. As soon as the user session terminates, the modified states of the packaged constructs are released, and the next time the user session starts up, the packaged constructs will be restored to their original state, until the user sessions modifies them again.

/by
, , ,

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