Sage-Code Laboratory
index<--

PL/SQL Packages

Working with packages is an important aspect of PL/SQL programming. Oracle provide standard packages that can be used in your applications. You need to learn how to work with packages to improve your productivity.

What is a Package?

A package is a schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions. A package is compiled and stored in the database, where many applications can share its contents. A package always has a specification, which declares the public items that can be referenced from outside the package but not always has a package body.

Building Applications

You implement a PL/SQL application as several packages—one package that provides the application programming interface (API) and helper packages to do the work. You want clients to have access to the API, but not to the helper packages.

In either the package specification or package body, you can map a package subprogram to an external Java or C subprogram by using a call specification, which maps the external subprogram name, parameter types, and return type to their SQL counterparts.

Package Specification

A package specification declares public items. The scope of a public item is the schema of the package. A public item is visible everywhere in the schema. To reference a public item that is in scope but not visible, qualify it with the package name.

Each public item declaration has all information needed to use the item. For example a function that can be used in other packages must have its signature duplicated. This include function name, parameters and result type. Sometimes may look inconvenient to have duplicated information, becouse in the package body you also must have the same exact declaration. But do not warry, compilation will fail if the signatures do not match.

To create a package specification, use statement: "CREATE OR REPLACE PACKAGE <NAME AS>" follow by code. This statement is used at the beggining of a script file that terminate with symbol "/", that means: run or execute. Without this character the package would not be installed when you run the script

Package Body

The package body contains the implementation of all items that makes the package work. It can contain local declarations that are not public until they are also contained in the package specification. To create package body you must use the statement: "CREATE OR REPLACE PACKAGE BODY <NAME AS>" follow by code.

Script File

The source code for PL/SQL packages can have any name usually followed by extension *.pkc. The package specification and package body can be stored in separate scripts with extensions: *.pks, *.pkb. After package specification we have first "/", after body we have second "/" that execute the two parts.

Example:

One single file: demo.pkc

CREATE PACKAGE emp_bonus AS
  PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
END emp_bonus;
/

CREATE PACKAGE BODY emp_bonus AS
  -- DATE does not match employees.hire_date%TYPE
  PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE
      ('Employees hired on ' || date_hired || ' get bonus.');
  END;
END emp_bonus;
/

Two files. Specification script: emp_bonus.pks

CREATE PACKAGE emp_bonus AS
  PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE);
END emp_bonus;
/

Second file: Body script: emp_bonus.pkb

CREATE PACKAGE BODY emp_bonus AS
  -- DATE does not match employees.hire_date%TYPE
  PROCEDURE calc_bonus (date_hired employees.hire_date%TYPE) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE
      ('Employees hired on ' || date_hired || ' get bonus.');
  END;
END emp_bonus;
/

Using SQL*Plus

So far we have not mention anything about SQL command line application (CLI). This is called SQL*Plus and is launch as any OS console program or interpreter. After you start sqlplus application you will be invited to login to a database. You can provide this information then you get a command prompt: sql:> Then you can run scripts or SQL*Plus commands. 

~/> sqlplus
...
sql:>

Now you can run scripts using @ symbol.

@{url|file_name[.ext] } [arg...]

Example:

Here is a wrapper script that can run both two other files that can be used to create package: emp_bonus. File name is: emp_bonus.sql, it could be emp_bonus.pk depending on your project conventions.

-- call other scripts from this script
@emb_bonus.pks;
@emb_bonus.pkb;

Start the SQL*Plus application, connect and run the script in a single OS command.

~/> sqlplus user/password@database_sid @emp_bonus.sql

Note: SQL*Plus is available on Windows and Linux after you install Oracle Client application. You can create basch scripts that install an entire database with all objects by calling a single SQL*Plus master script. You should learn all about SQL*Plus utility in order to make these scripts: SQL*Plus User's Guide

STANDARD Package

A package named STANDARD defines the PL/SQL environment. The package specification declares public types, variables, exceptions, subprograms, which are available automatically to PL/SQL programs. In standard package Most SQL functions are overloaded. For example, package STANDARD contains these declarations:

-- overloaded function to_char
FUNCTION TO_CHAR (right DATE) RETURN VARCHAR2;
FUNCTION TO_CHAR (left NUMBER) RETURN VARCHAR2;
FUNCTION TO_CHAR (left DATE, right VARCHAR2) RETURN VARCHAR2;
FUNCTION TO_CHAR (left NUMBER, right VARCHAR2) RETURN VARCHAR2;

Oracle Packages

Oracle supplies many PL/SQL packages with the Oracle server to extend database functionality and provide PL/SQL access to SQL features. You can use the supplied packages when creating your applications or for ideas in creating your own stored procedures. Most of these packages have names starting with prefix: DBMS_* but some other prefixes are used, for example: SDO_* and UTL_*

To call a function or to use other elements from these packages you must use dot syntax, for example: DBMS_OUTPUT.PUT_LINE("Output Text"); can be used to print to standard output a line of text.

The END

This was the basic introduction to Oracle PL/SQL language. To learn more details about this language there is no better place to learn but the Oracle reference doc. Please continue with next link. This is the entry point for PL/SQL documentation.

Read next: Oracle Manual: PL/SQL Packages & Types