PL/SQL - Packages

Oracle Packages:
1.1   A Package is a collection of logically related data objects and the procedures that manipulate them.  A package is a schema object which includes definitions of variables, constants, cursors, exceptions, exception handlers, composite data types, procedures, and functions, and is stored in the database as a single object.  Variables and cursors can be declared global to all procedures in a package and they maintain their values throughout the session.
A Package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects.
1.2   A package has two parts  
·         Package Specification ( Called as spec in short) or package header
·         Package Body.
1.3 Package Specification acts as an interface to the package and contains the following.  Package specification does not contain any code.
·         Declaration of types
·         Variables
·         Constants
·         Exceptions
·         Cursors
·         Subprograms 
1.4   Use of Packages:
 Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification or spec.   PL/SQL stored packages allow you to define a functional structure well beyond what can be accomplished with alternative systems. A package can include definitions of variables, constants, cursors, exceptions, exception handlers, composite data types, procedures, and functions, and is stored in the database as a single object.  Variables and cursors can be declared global to all procedures in a package and they maintain their values throughout the session.
 
In other implementations, variables lose all state between calls.  Thus, you can define various packages of self-contained, reusable functionality.  For example, you can define an inventory package containing commonly used routines for shipping products or reordering parts, an accounting package containing  routines for tracking expenses and budgets, and a human resources package for hiring and promoting employees and tracking their progress.
 
The specification of a package (i.e., the procedure names and parameter lists) can be separate from its actual implementation.  This enables you to specify an entire application on up-front, yet write and test the procedures one part at a time.  
 
Package objects can be made public (accessible to other packages), or private (accessible to only its own package). This allows you to hide implementation details of your own system from the  public and provides security. 
 
 
If changing the implementation of a package does not require changing its specification, then any procedures that call routines in this package do not need to be recompiled. This avoids a chain reaction where the modification of one package sets off hundreds of automatic recompilation throughout the system, and frees database resources for other useful work.
 
The following are some of the uses of packages
 
The Packages allows a PL/SQL block to group together related items, types and subprograms as a module.  When a procedure in a package is called entire package is loaded, though it happens to be expensive first time the response is faster for subsequent calls (Making one time compilation and calling the package when ever necessary  through) . 
  • Package allows us to create types, variable and subprograms that are private or public as per requirement.
·         Package contains all related code in a single object.
·         All related code loaded into memory simultaneously of a package when it is created.
·         As a Package body Single object compilation is performed.
·         For a given package variables defined and which persist for term of session.
·         Fewer   objects to manage and grant/revoke privileges.

1.5 Advantages of Packages

Packages offer several advantages:
  • Modularity
  • Easier Application Design
  • Information hiding
  • Added functionality
  • Better performance.

Modularity

Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined. This aids application development.

Easier Application Design

When designing an application, all you need initially is the interface information in the package specs. You can code and compile a spec without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package bodies fully until you are ready to complete the application.

Information Hiding

With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible). For example, if a package contains four subprograms, three might be public and one private. The package hides the definition of the private subprogram so that only the package (not your application) is affected if the definition changes. This simplifies maintenance and enhancement. Also, by hiding implementation details from users, you protect the integrity of the package.

Added Functionality

Packaged public variables and cursors persist for the duration of a session. So, they can be shared by all subprograms that execute in the environment. Also, they allow you to maintain data across transactions without having to store it in the database.

    Better Performance

When a package is called with subprogram for the first time, the whole package is loaded into memory. So, later calls to related subprograms in the package require no disk I/O. Also, packages stop cascading dependencies and thereby avoid unnecessary recompiling. For example, if you change the definition of a packaged function, Oracle need not recompile the calling subprograms because they do not depend on the package body.
    Syntax of  the PACKAGE.
   CREATE [OR REPLACE] PACKAGE package_name
  [AUTHID {CURRENT_USER | DEFINER}] {IS | AS}
  [type_definition [type_definition] ...]
  [cursor_spec [cursor_spec] ...]
  [item_declaration [item_declaration] ...]
  [{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...]
  END [package_name];
  

1.6 The Package Specification.

The package spec contains public declarations. The scope of these declarations is local to your database schema and global to the package. So, the declared items are accessible from your application and from anywhere in the package.


       Diagram-1.1   How   schema is Package body and Package specifications are connected.

Always create and compile specifications of the Package and   Package body separately. 

 

  Package syntax

The general syntax for the package body is shown below:
CREATE OR REPLACE PACKAGE package_name
IS
   PROCEDURE procdure_name1;
   PROCEDURE procdure_name2;
   PROCEDURE procdure_name3;
  
END PACK1;
/ 

1.8 Package body syntax

The general syntax for the package body is shown below:
PACKAGE BODY package_name 
IS
   [ declarations of variables and types ]
   [ header and SELECT statement of cursors ]
   [ header and body of modules ]
[ BEGIN 
     executable statements ]
[ EXCEPTION
      exception handlers ]
END [package_name];
In the body you can declare other variables, but you do not repeat the declarations in the specification. The body contains the full implementation of cursors and modules. In the case of a cursor, the package body contains both the header and the SQL statement for the cursor. In the case of a module, the package body contains both the header and body of the module.
The BEGIN keyword indicates the presence of an execution or initialization section for the package. This section can also optionally include an exception section.
As with a procedure, function, and package specification, you can add the name of the package, as a label, after the END keyword in both the specification and package.
PACKAGE BODY Syntax.
 
 CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}
  [type_definition [type_definition] ...]
  [cursor_body [cursor_body] ...]
  [item_declaration [item_declaration] ...]
  [{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...]
  [BEGIN sequence_of_statements]
  END [package_name];
 
Package Body with   Procedures
CREATE OR REPLACE PACKAGE <package_name> AS

PROCEDURE <procedure_name1> (<parameters>);
PROCEDURE <procedure_name2> (<parameters>);
PROCEDURE <procedure_name3> (<parameters>);

END <package_name>;

Forward Declaration in Packages
In a package you can declare a group of   logical subprograms which are logically related in a package. The subprogram specs go in the package spec, and the subprogram bodies go in the package body, where they are invisible to applications. With this packages allow you to hide implementation details.
 
/* Here is the syntax of the Package and Procedure called Package_Name and Package body and three sample Procedures are declared and it has the three procedures called Package1, Package2, Package3*/
 
Syntax of PACKAGE 
 
CREATE PACKAGE <Package_Name> AS   -- package spec
   PROCEDURE Procedure1 (emp_id INTGER, name VARCHAR2 .......);
   PROCEDURE Procedure2 (emp_id INTEGER, amount REAL);
   PROCEDURE Procedure3 (emp_id INTEGER);
   ...
END <Package_Name>;
 
 
Syntax of PACKAGE BODY
 
CREATE PACKAGE BODY <Package_Body> AS -- package body
 PROCEDURE Procedure1 (Paramter1 INTGER, Paramter2 VARCHAR2......) IS
   BEGIN
      ...
      INSERT INTO <Table1> VALUES (v1, v2,.....);
   END Procedure1;
 
PROCEDURE Procedure2 (Paramter1 INTEGER, Paramter2 REAL) IS
      salary REAL;
   BEGIN
      SELECT Parameter1 INTO salary FROM emp 
         WHERE < Condition>;
      ...
   END Procedure2;
 
PROCEDURE Procedure3 (Paramter1 INTEGER) IS
   BEGIN
      DELETE FROM < TABLE1>
         WHERE < Condition>;
   END Procedure3;
   ...
END <Package_Body>;

/* This Package body where Procedure called main_p and ‘validate_input’ and get_api_input_parameters procedures */ 
     
 
                                  
             Figure.1 Creation of the Package


1.9   The following list offers a summary of the best practices for packages covered in this chapter:
·         Use a consistent and effective coding style. Use consistent indentation to reveal the logical flow of the program and to delineate the different sections of the PL/SQL program structure. Generally, this means that all executable statements are indented in from the BEGIN keyword, the body of a loop is indented within the LOOP and END LOOP keywords, and so on. Within a package, all specification declarations are indented between the IS and END keywords.
Code all reserved words in the PL/SQL language in upper-case. Use lower-case for all application-specific identifiers. Generally, this is accomplished with hard-coded literals and the use of UPPER and LOWER.
Use comments to add value to the code. Don't bother with comments that simply repeat what the code clearly states.
The style elements found valuable particularly for packages include the following:
Use banners (specially formatted comment lines) to mark clearly the different groupings of package elements.
Use end labels for the package and for all program units defined in the package body.

Example: A Generated Package Template

SQL> exec PLVgen.pkg('emp_maint');
  1 CREATE OR REPLACE PACKAGE emp_maint
  2 /*
  3 || Program: emp_maint
  4 ||  Author: Steven Feuerstein
  5 ||    File: emp_maint.SQL
  6 || Created: APR 13, 1996 18:56:59
  7 */
  8 /*HELP
  9 Add help text here...
 10 HELP*/
 11
 12 /*EXAMPLES
 13 Add help text here...
 14 EXAMPLES*/
 15
 16 IS
 17 /* Public Data Structures */
 18
 19 /* Public Programs */
 20
 21    PROCEDURE help (context_in IN VARCHAR2 := NULL);
 22
 23 END emp_maint;
 24 /
 25
 26 CREATE OR REPLACE PACKAGE BODY emp_maint
 27 IS
 28 /* Private Data Structures */
 29
 30 /* Private Programs */
 31
 32 /* Public Programs */
 33
 34    PROCEDURE help (context_in IN VARCHAR2 := NULL)
 35    IS
 36    BEGIN
 37       PLVhlp.show ('s:emp_maint', context_in);
 38    END help;
 39 END emp_maint;
 40 /
Below are the features to highlight in the above package template:
Lines
Significance
2-7
A standard header for the package, showing the author, filename, and date created.
8-14
Stubs for help text. These comment blocks provide both inline code documentation and help text to users.
17-19
Banners to identify the two main kinds of elements that can appear in a package specification: data structures and program units.
21
Header for a procedure that delivers online help for this package. Of course, this should only be included if the online help package is being used.
23
The END statement with the package name appended.
28-32
Banners to identify the three kinds of elements that can appear in a package body: private data structures, program units, and the implementation of the public program units.
34-38
The implementation of the help procedure. Notice that the procedure uses an end label with the program name and is also indented in multiple steps from the overall package.

·         Selecting Package Names:  
o   Choose appropriate and accurate names
§  There are two aspects to coming up with the right names for your code elements:
§  The structure of the name should match the role that element plays in your code.
§  The name should reflect what the element does in your code.
o   Avoiding Redundancy
§  To reference a package element outside of the package you must use dot notation (package.element). As a result, you will want to avoid redundancy in your package and element names. For example, suppose I have a package named emp_maint for employee maintenance. One of the procedures in the package sets the employee salary.

·         Construct the optimal interface to your package.
o   Design your package so that it is easy -- and a pleasure -- to use. When you build packages for reuse, other PL/SQL developers become your users of it. Treat them with respect. Make the parameters in your programs case-insensitive. Don't require users to know about and pass literal values.
o   Make the programs case-insensitive
Make sure users don't trip over senseless obstacles on the path to using your programs. A common source of frustration is the requirement that arguments to a program be in one case or another (usually upper or lower).
Consider the following program:
CREATE OR REPLACE FUNCTION twice 
   (string_in IN VARCHAR2, action_in IN VARCHAR2)
RETURN VARCHAR2
IS
BEGIN
   IF action_in = 'UL'
   THEN
      RETURN (UPPER (string_in) || LOWER (string_in));      
      
   ELSIF action_in = 'LU'
   THEN
      RETURN (LOWER (string_in) || UPPER (string_in));      
      
   ELSIF action_in = 'N' 
   THEN
      RETURN string_in || string_in;
   END IF; 
END twice;
/
·         Build flexibility directly into your packages.
o    If a program is going to be widely reusable, it should be able to adapt to different circumstances to meet different needs. There are two basic ways to be flexible when writing programs for others to use:
§  Offer lots of parameters in the parameter lists of the package's functions and procedures. This is the traditional, well-worn path.
§  Provide toggles or on-off switches, distinct from the main programs of the package, which modify the behavior of those programs. This approach takes advantage of the package structure to offer a new way of doing things.
§  One of the most exciting benefits of package toggles is that they allow a user of the package to modify the behavior of the package without changing any application code that calls the package element.
§  You didn't have to change your program and you didn't have to modify the state of your database. From outside the package, you call the toggle program to reach inside the package and change the way the package will behave. This ability to leave your own code intact comes in particularly handy not only for special exceptions but also for testing as below:
A common debug and test cycle in PL/SQL shops goes like this:
1.      You identify incorrect behavior in your program.
2.      Unable to understand the cause of the behavior, you place numerous calls to DBMS_OUTPUT.PUT_LINE (or, with your purchase of this book, PL/Vision's much more friendly p.l procedure) and other kinds of tracing lines of code so that you can see what is going on.
3.      You analyze the output, track down the problem, and fix it.
4.      You finally decide that all the bugs are gone.
5.      You notify your manager that the application is ready to go. Excitement mounts. Other organizations are told to start moving the code from test to production. Suddenly, you break out in a cold sweat and tell your bewildered manager to "hold off a minute."
6.      You forgot about all that debugging code you littered into your application. It can't go into production like that. You have to go back into the program to comment out or outright remove all that trace code. No problem, you tell yourself. Easy to do...but there could be a problem. After all, any time you touch the code, you can break it. After any changes of any kind to your code, you really should retest.
7.      So you have to go back to your manager and ask for more time to make sure everything really is all right. Not a pleasant situation in which to find yourself.
If, on the other hand, you used packages with toggles to trace your debugging activity, you would not have to worry about any of that. You could keep your code intact and simply issue a call to the appropriate package toggle to turn off any superfluous activity, as in:
SQL> exec pkg.turn_off
·         Overload for smart packages.
o    One of the most powerful aspects of the package is the ability to overload program units. When you overload, you define more than one program with the same name. These programs will differ in other ways (usually the number and types of parameters) so that at runtime the PL/SQL engine can figure out which of the programs to execute. You can take advantage of the overloading feature of packages to make your package-based features as accessible as possible.
o    Does overloading sound unfamiliar or strange? Well, have you ever used the TO_CHAR function? If so, then you have already been enjoying the creature comforts of overloading. TO_CHAR converts both numbers and dates to strings. Have you ever wondered why you don't have to call functions with names like TO_CHAR_FROM_DATE or TO_CHAR_FROM_NUMBER? Probably not. You probably just took TO_CHAR for granted, and that is how it should be.
o    In reality, there are two different TO_CHAR functions (both defined in the STANDARD package): one to convert dates and another to convert numbers. The reason that you don't have to care about such details and can simply execute TO_CHAR is that the PL/SQL runtime engine examines the kind of data you pass to TO_CHAR and then automatically figures out which of the two functions (with the same name) to execute. It's like magic, only it's better than magic: it's intelligent software!
o    When you build overloaded modules, you spend more time in design and implementation than you might with separate, standalone modules. This additional up-front time will be repaid handsomely down the line in program productivity and ease of use.
o    You will not have to try to remember the different names of the modules and their specific arguments. Properly constructed, overloaded modules will have anticipated the different variations, hidden them behind a single name, and liberated your brain for other, more important matters.
When to overload a package:
o    When you overload, you take the first step towards providing a declarative interface to PL/SQL-based functionality. With a declarative approach, a developer does not write a program to obtain the necessary functionality. Instead, she describes what she wants and lets the underlying code handle the details (this follows the approach used by the SQL language). The process of overloading involves abstracting out from separate programs into a single action.
o    You want to display a date? You want to display a number? You want to display a string and a number? Hold on a minute. The common element is that you want to display something -- lots of somethings, in fact. So don't create display_date, display_string, etc. procedures. Instead, offer a single display procedure, which is in fact many overloaded display procedures.
o    With the overloading in place, your user must only remember this: when I want to display something, I simply ask the display program to take care of it for me. What do I pass to it? Whatever I want it to display. I will not (and do not have to) worry about the how of the display mechanism. Those details are hidden from me.
o    Here are some of the circumstances that cause the PL/SQL fairy to whisper in my ear "Overload, overload...:
§  Apply the same action to different kinds or combinations of data.
§  Allow developers to use a program in the most natural and intuitive fashion; you use overloading to fit your program to the needs of the user.
§  Make it easy for developers to specify, unambiguously and simply, the kind of action desired.

                  Package Name
          Description
DBMS_OUTPUT
Displays output from PL/SQL programs to the terminal. The "lowest common denominator" debugger mechanism for PL/SQL code.
UTL_FILE
Allows PL/SQL programs to read from and write to operating system files.
DBMS_JOB
Used to submit and manage regularly scheduled jobs for execution inside the database.
DBMS_LOCK
Allows users to create their own locks using the Oracle Lock Management (OLM) services in the database.
DBMS_MAIL
Offers an interface to Oracle Office (previously known as Oracle Mail).
DBMS_ALERT
Provides support for notification of database events on an asynchronous basis. Registers a process with an alert and then waits for a signal from that alert.
DBMS_PIPE
Allows communication between different Oracle sessions through a pipe in the RDBM’s shared memory. One of the few ways to share memory-resident data between Oracle sessions.
DBMS_SESSION
Provides a programmatic interface to several SQL ALTER SESSION commands and other session-level commands.
DBMS_SNAPSHOT
A programmatic interface through which you can manage snapshots and purge snapshot logs. You might use modules in this package to build scripts to automate maintenance of snapshots.
DBMS_SQL
Full support for dynamic SQL within PL/SQL. Dynamic SQL means SQL statements that are not prewritten into your programs. They are, instead, constructed at runtime as character strings and then passed to the SQL Engine for execution. (PL/SQL Release 2.1 only)
DBMS_TRANSACTION
A programmatic interface to a number of the SQL transaction statements, such as the SET TRANSACTION command.
DBMS_UTILITY
The "miscellaneous" package. Contains various useful utilities, such as GET_TIME, which calculates elapsed time to the hundredth of a second, and FORMAT_CALL_STACK, which returns the current execution stack in the PL/SQL runtime engine.
DBMS_ALERT
Provides support for notification of database events on an asynchronous basis. Registers a process with an alert and then waits for a signal from that alert.
Figure2. Some of the Built-in Packages Stored in the Oracle Database



           Figure3. Package creation at  SQL PLUS prompt.
2.0   More about the UTL_FILE
The UTL_FILE package lets your PL/SQL programs read and write operating system (OS) text files. It provides a restricted version of standard OS stream file input/output (I/O).

The file I/O capabilities are similar to those of the standard operating system stream file I/O (OPEN, GET, PUT, CLOSE), with some limitations. For example, call the FOPEN function to return a file handle, which you then use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When you are done performing I/O on the file, call FCLOSE to complete any output and to free any resources associated with the file.

The following are UNIX Specific.

UTL_FILE_DIR=/appl/gl/log
UTL_FILE_DIR=/appl/gl/out
 
Then, the following file locations and filenames are valid:
FILE LOCATION             FILENAME
/appl/gl/log              L10324.log
/appl/gl/out              O10324.out
 
But, the following file locations and filename are invalid:
FILE LOCATION             FILENAME
/appl/gl/log/backup       L10324.log          # subdirectory
/APPL/gl/log              L10324.log          # uppercase
/appl/gl/log              backup/L10324.log   # dir in name
/usr/tmp                  T10324.tmp          # not in INIT.ORA


Types
 
TYPE file_type IS RECORD (id BINARY_INTEGER);
The contents of FILE_TYPE are private to the UTL_FILE package. Users of the package should not reference or change components of this record.
Exceptions in   UTL_FILE Package Exceptions
    Exception 

       Description
INVALID_PATH

File location or filename was invalid.  
INVALID_MODE

The open_mode parameter in FOPEN was invalid.  
INVALID_FILEHANDLE

File handle was invalid.  
INVALID_OPERATION

File could not be opened or operated on as requested.  
READ_ERROR

Operating system error occurred during the read operation.  
WRITE_ERROR

Operating system error occurred during the write operation.  
INTERNAL_ERROR

Unspecified PL/SQL error.  
INVALID_PATH

File location or filename was invalid.  
Subprograms in the UTL_FILE Package
Subprogram     
Description 
 
FOPEN function

Opens a file for input or output with the default line size.  
IS_OPEN function

Determines if a file handle refers to an open file.  
FCLOSE procedure

Closes a file.  
FCLOSE_ALL procedure

Closes all open file handles.  
GET_LINE procedure

Reads a line of text from an open file.  
PUT procedure

Writes a line to a file. This does not append a line terminator.  
NEW_LINE procedure

Writes one or more OS-specific line terminators to a file.  
PUT_LINE procedure

Writes a line to a file. This appends an OS-specific line terminator.  
PUTF procedure

A PUT procedure with formatting.  
FFLUSH procedure

Physically writes all pending output to a file.  
FOPEN function

Opens a file with the maximum line size specified.  
FOPEN function

Opens a file for input or output with the default line size.  

2.1        ALTER PACKAGE.
 
ALTER PACKAGE command 
 
PURPOSE:      To recompile a stored package. 
 
SYNTAX:  ALTER PACKAGE [schema.]package 
                   COMPILE [PACKAGE | BODY] 
 
Schema  :  The schema contains the package.  If you omit schema, Oracle 
    assumes the package is in your own schema. 
 
Package  : The name of the package to be recompiled. 
 
COMPILE  :    Recompiles the package specification or body.  The COMPILE keyword 
                   is required. 
 
PACKAGE  : Recompiles the package body and specification. 
 
BODY  :  Recompiles only the package body. 
 
The default option is PACKAGE. 
 
PREREQUISITES:      The package must be in your own schema or you must have ALTER ANY PROCEDURE system privilege.
<<<Previous                                                                                                                                                                                  Next>>>

Complete Tutorial

*/