Packages

 A package can be defined as a collection of related program objects such as procedures, functions, and associated cursors and variables together as a unit in the database. In simpler term, a package is a group of related procedures and functions stored together and sharing common variables, as well as local procedures and functions. A package contains two separate parts: the package specification and the package body. The package specification and package body are compiled separately and stored in the data dictionary as two separate objects. The package body is optional and need not to be created if the package specification does not contain any procedures or functions. Applications or users can call packaged procedures and functions explicitly similar to standalone procedures and functions. Advantages of Packages Packages offer a lot of advantages. They are as follows. 1. Stored packages allow us to sum up (group logically) related stored procedures, variables, and data types, and so forth in a single-named, stored unit in the database. This provides for better orderliness during the development process. In other words packages and its modules are easily understood because of their logical grouping. 2. Grouping of related procedures, functions, etc. in a package also make privilege management easier. Granting the privilege to use a package makes all components of the package accessible to the grantee. 3. Package helps in achieving data abstraction. Package body hides the details of the package contents and the definition of private program objects so that only the package contents are affected if the package body changes. 4. An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for standalone procedures. Therefore, when calls to related packaged procedures occur, no disk I/O is necessary to execute the compiled code already in memory. This results in faster and efficient operation of programs. 5. Packages provide better performance than stored procedures and functions because public package variables persist in memory for the duration of a session. So that they can be accessed by all procedures and functions that try to access them. 6. Packages allow overloading of its member modules. More than one function in a package can be of same name. The functions are differentiated, depending upon the type and number of parameters it takes. 5.14 Packages 253 Units of Packages As described earlier, a package is used to store together, the logically related PL/SQL units. In general, following units constitute a package. – Procedures – Functions – Triggers – Cursors – Variables Parts of Package A Package has two parts. They are: – Package specification – Package body Package Specification The specification declares the types, variables, constants, exceptions, cursors, and subprograms that are public and thus available for use outside the package. In case in the package specification declaration there is only types, constants, exception, or variables, then there is no need for the package body because package specification are sufficient for them. Package body is required when there is subprograms like cursors, functions, etc. Package Body The package body fully defines subprograms such as cursors, functions, and procedures. All the private declarations of the package are included in the package body. It implements the package specification. A package specification and the package body are stored separately in the database. This allows calling objects to depend on the specification only, not on both. This separation enables to change the definition of program object in the package body without causing Oracle to interfere with other objects that call or reference the program object. Oracle invalidates the calling object if the package specification is changed. Creating a Package A package consists of package specification and package body. Hence creation of a package involves creation of the package specification and then creation of the package body. The package specification is declared using the CREATE PACKAGE command. 254 5 PL/SQL The syntax for package specification declaration is as follows. CREATE[OR REPLACE] PACKAGE [AS/IS] PL/SQL package specification All the procedures, sub programs, cursors declared in the CREATE PACKAGE command are described and implemented fully in the package body along with private members. The syntax for declaring a package body is as follows: CREATE[OR REPLACE] PACKAGE BODY [AS/IS] PL/SQL package body Member functions and procedures can be declared in a package and can be made public or private member using the keywords public and private. Use of all the private members of the package is restricted within the package while the public members of the package can be accessed and used outside the package. Referencing Package Subprograms Once the package body is created with all members as public, we can access them from outside the program. To access these members outside the packages we have to use the dot operator, by prefixing the package object with the package name. The syntax for referencing any member object is as follows: . To reference procedures we have to use the syntax as follows: EXECUTE .; But the package member can be referenced by only its name if we reference the member within the package. Moreover the EXECUTE command is not required if procedures are called within PL/SQL. Functions can be referenced similar to that of procedures from outside the package using the dot operator. Public and Private Members of a Package A package can consist of public as well as private members. Public members are those members which are accessible outside the package, whereas the private members are accessible only from within the package. Private members are just like local members whose are not visible outside the enclosing code block (in this case, a package). 5.15 Exceptions Handling 255 The place where a package member is declared, also matters in deciding the visibility of that member. Those members whose declaration is found in the package specification are the public members. The package members that are not declared in the package specification but directly defined in the package body become the private members. Viewing Existing Procedural Objects The source code for the existing procedures, functions, and packages can be queried from the following data dictionary views. USER SOURCE Procedural objects owned by the user. ALL SOURCE Procedural objects owned by the user or to which the user has been granted access. DBA SOURCE Procedural objects in the database. Removing a Package A package can be dropped from the database just like any other table or database object. The exact syntax of the command to be used for dropping a package is: DROP PACKAGE ; To drop a package a user either must own the package or he should have DROP ANY PACKAGE privilege. 5.15 Exceptions Handling During execution of a PL/SQL block of code, Oracle executes every SQL sentence within the PL/SQL block. If an error occurs or an SQL sentence fails, Oracle considers this as an Exception. Oracle engine immediately tries to handle the exception and resolve it, by raising a built-in Exception handler. Introduction to Exceptions One can define an EXCEPTION as any error or warning condition that arises during runtime. The main intention of building EXCEPTION technique is to continue the processing of a program even when it encounters runtime error or warning and display suitable messages on console so that user can handle those conditions next time. In absence of exceptions, unless the error checking is disabled, a program will exit abnormally whenever some runtime error occurs. But with exceptions, 256 5 PL/SQL if at all some error situation occurs, the exceptional handler unit will flag an appropriate error/warning message and will continue the execution of program and finally come out of the program successfully. An exception handler is a code block in memory that attempts to resolve the current exception condition. To handle very common and repetitive exception conditions Oracle has about 20 Named Exception Handlers. In addition to these for other exception conditions Oracle has about 20,000 Numbered Exception Handlers, which are identified by four integers preceded by hyphen. Each exception handler, irrespective of how it is defined, (i.e., by Name or Number) has code attached to it that attempts to resolve the exception condition. This is how Oracle’s Internal Exception handling strategy works. Oracle’s internal exception handling code can be overridden. When this is done Oracle’s internal exception handling code is not executed but the code block that takes care of the exception condition, in the exception section, of the PL/SQL block is executed. As soon as the Oracle invokes an exception handler the exception handler goes back to the PL/SQL block from which the exception condition was raised. The exception handler scans the PL/SQL block for the existence of exception section within the PL/SQL block. If an exception section within the PL/SQL block exists the exception handler scans the first word, after the key word WHEN, within the exception section. If the first word after the key word WHEN is the exception handler’s name then the exception handler executes the code contained in the THEN section of the construct, the syntax follows: EXCEPTION WHEN exception name THEN User defined action to be carried out. Exceptions can be internally defined (by the run-time system) or user defined. Internally defined exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise internally defined exceptions. Raised exceptions are handled by separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment. Advantages of Using Exceptions 1. Control over abnormal exits of executing programs on encountering error conditions, hence the behavior of application becomes more reliable. 2. Meaningful messages can be flagged so that the developer can become aware of error and warning conditions and act upon them. 3. In traditional error checking system, if same error is to be checked at several places, you are required to code the same error check at all those 5.15 Exceptions Handling 257 places. But with exception handling technique, we will write the exception for that particular error only once in the entire code. Whenever that type error occurs at any place in code, the exceptional handler will automatically raise the defined exception. 4. Being a part of PL/SQL, exceptions can be coded at suitable places and can be coded isolated like procedures and functions. This improves the overall readability of a PL/SQL program. 5. Oracle’s internal exception mechanism combined with user-defined exceptions, considerably reduce the development efforts required for cumbersome error handling. Predefined and User-Defined Exceptions As discussed earlier there are some predefined or internal exceptions, and a developer can also code user-defined exceptions according to his requirement. In next session we will be looking closely at these two types of exceptions. Internally (Predefined) Defined Exceptions An internal exception is raised implicitly whenever a 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 a name for some common errors to raise them as exception. For example, if a SELECT INTO statement returns no rows, PL/SQL raises the predefined exception NO DATA FOUND, which has the associated Oracle error number ORA-01403. Example Figure 5.21 shows the internally defined exception NO DATA FOUND, when we want to get a salary of an employee who is not in the EMP table. If we execute this query with some emp name say “XYZ” as input and if emp name column of employee table does not contain any value “XYZ,” Oracle’s internal exception handling mechanism will raise NO DATA FOUND exception even when we have not coded for it. PL/SQL declares predefined exceptions globally in package STANDARD, which defines the PL/SQL environment. Some of the commonly used exceptions are as follows: User Defined Exceptions Unlike internally defined exceptions, user-defined exceptions must be declared and raised explicitly by RAISE statements. Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package. An exception is declared by introducing its name, followed by the keyword EXCEPTION. 258 5 PL/SQL Name of the exception Raised when ... ACCESS INTO NULL Your program attempts to assign values to the attributes of an uninitialized (atomically null) object. COLLECTION IS NULL Your program attempts to apply collection methods, other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray. CURSOR ALREADY OPEN Your program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers. So, your program cannot open that cursor inside the loop. DUP VAL ON INDEX Your program attempts to store duplicate values in a database column that is constrained by a unique index. INVALID CURSOR Your program attempts an illegal cursor operation such as closing an unopened cursor. INVALID NUMBER In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE ERROR is raised.) LOGIN DENIED Your program attempts to log on to Oracle with an invalid username and/or password. NO DATA FOUND A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function will never raise NO DATA FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised. NOT LOGGED ON Your program issues a database call without being connected to Oracle. 5.15 Exceptions Handling 259 Continued. Name of the exception Raised when ... ROWTYPE MISMATCH The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible. PROGRAM ERROR PL/SQL has an internal problem. SELF IS NULL Your program attempts to call a MEMBER method on a null instance. That is, the builtin parameter SELF (which is always the first parameter passed to a MEMBER method) is null. STORAGE ERROR PL/SQL runs out of memory or memory has been corrupted. SUBSCRIPT BEYOND COUNT Your program references a nested table or varray element using an index number larger than the number of elements in the collection. SUBSCRIPT OUTSIDE LIMIT Your program references a nested table or varray element using an index number (−1 for example) that is outside the legal range. SYS INVALID ROWID The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid. TIMEOUT ON RESOURCE A time-out occurs while Oracle is waiting for a resource. TOO MANY ROWS A SELECT INTO statement returns more than one row. VALUE ERROR An arithmetic, conversion, truncation, or size constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE ERROR. In procedural statements, VALUE ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID NUMBER is raised.) ZERO DIVIDE Your program attempts to divide a number by zero. 260 5 PL/SQL Fig. 5.21. Internally defined exception The syntax is as follows: DECLARE EXCEPTION; Exceptions are declared in the same way as the variables. But exceptions cannot be used in assignments or SQL expressions/statements as they are not data items. The visibility of exceptions is governed by the same scope rules which apply to variables also. Raising User-Defined and Internal Exceptions As seen in the previous example, one can notice a statement “RAISE Exception1.” This statement is used to explicitly raise the exception “Exception1,” the reason being, unlike internally defined exceptions which are automatically raised by “OracleS” run time engine, user-defined exceptions have to be raised explicitly by using RAISE statement. However, it is always possible to RAISE predefined (internally defined) exceptions, if needed, in the same way as do the user-defined exceptions, which is illustrated in Fig. 5.22 RAISE ; 5.15 Exceptions Handling 261 Fig. 5.22. Exception example Example Create a table as follows, CREATE TABLE ROOM STATUS (ROOM NO NUMBER(5) PRIMARY KEY, CAPACITY NUMBER(2), ROOMSTATUS VARCHAR2(20), RENT NUMBER(4), CHECK (ROOMSTATUS IN (‘VACANT’,‘BOOKED’))); User-Defined Error Reporting – Use of Raise Application Error RAISE APPLICATION ERROR lets display the messages we want whenever a standard internal error occurs. RAISE APPILCATION ERROR associates an Oracle Standard Error Number with the message we define. The syntax for RAISE APPLICATION ERROR is as follows: RAISE APPLICATION ERROR (Oracle Error Number, Error Message, TRUE/FALSE); 262 5 PL/SQL Fig. 5.23. Without exception Fig. 5.24. Execution of exception Figures 5.23 and 5.24 shows the output for two conditions ‘Room Available’ and ‘Vacant’. 5.15 Exceptions Handling 263 Oracle error number is the standard Oracle error (−20000 to −20999) that we want to associate with the message (max 2,048 kb) defined, TRUE/FALSE indicates whether to place the error message on previous error stack (TRUE) or to replace all the errors with this message (FALSE). RAISE APPLICATION ERROR can be called only from an executing subprogram. As soon as the subprogram encounters RAISE APPLICATION ERROR, the subprogram returns control back to the calling PL/SQL code thereby displaying the error message. We can handle the exception raised in the calling portion of PL/SQL block.