PL/SQL Packages

Package Concepts

A package is a database object that groups logically related PL/SQL types, variables, and subprograms. You specify the package and then define its body in separate steps.

The package specification is the interface to the package, declaring the public types, variables, constants, exceptions, cursors, and subprograms that are visible outside the immediate scope of the package. The body defines the objects declared in the specification, queries for the cursors, code for the subprograms, and private objects that are not visible to applications outside the package.

TimesTen stores the package specification separately from the package body in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body.

Note:

The syntax for creating packages and package bodies is the same as in Oracle Database; however, while Oracle Database documentation mentions that you must run a script named DBMSSTDX.SQL, this does not apply to TimesTen.

Creating and Using Packages

In TimesTen, create packages and store them permanently in a TimesTen database by using the CREATE PACKAGE and CREATE PACKAGE BODY statements.

To create a new package, do the following:

Create the package specification with the CREATE PACKAGE statement.

You can declare program objects in the package specification. Such objects are referred to as public objects and can be referenced outside the package, and by other objects in the package.

Optionally use CREATE OR REPLACE PACKAGE if you want the package specification to be replaced if it already exists.
Create the package body with the CREATE PACKAGE BODY (or CREATE OR REPLACE PACKAGE BODY) statement.

You can declare and define program objects in the package body.
- You must define public objects declared in the package specification.
- You can declare and define additional package objects, referred to as private objects. Private objects are declared in the package body rather than in the package specification, so they can be referenced only by other objects in the package. They cannot be referenced outside the package.

Use ALTER PACKAGE to explicitly compile the member procedures and functions of a package or modify the compilation options.

For more information on the CREATE PACKAGE, CREATE PACKAGE BODY, and ALTER PACKAGE statements, see SQL Statements in Oracle TimesTen In-Memory Database SQL Reference.

Note:

ALTER is not supported for PL/SQL objects in TimesTen Scaleout.
If you use replication: PL/SQL DDL statements, such as CREATE statements for PL/SQL functions, procedures, and packages, are not replicated. See Creating a New PL/SQL Object in an Existing Active Standby Pair and Adding a PL/SQL Object to an Existing Classic Replication Scheme in Oracle TimesTen In-Memory Database Replication Guide for steps to address this.
See Showing Errors in ttIsql for how to get information when you encounter errors in compiling a package.

For the following example, consider the case where you want to add a row to the employees tables when you hire a new employee and delete a row from the employees table when an employee leaves your company. The following example creates two procedures to accomplish these tasks and bundles the procedures in a package. The package also contains a function to return the count of employees with a salary greater than that of a specific employee. The example then executes the function and procedures and verifies the results.

Command> CREATE OR REPLACE PACKAGE emp_actions AS
           PROCEDURE hire_employee (employee_id NUMBER,
             last_name VARCHAR2,
             first_name VARCHAR2,
             email VARCHAR2,
             phone_number VARCHAR2,
             hire_date DATE,
             job_id VARCHAR2,
             salary NUMBER,
             commission_pct NUMBER,
             manager_id NUMBER,
             department_id NUMBER);
           PROCEDURE remove_employee (emp_id NUMBER);
           FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER;
         END emp_actions;
         /
 
Package created.
 
Command> -- Package body:
         CREATE OR REPLACE PACKAGE BODY emp_actions AS
         -- Code for procedure hire_employee:
           PROCEDURE hire_employee (employee_id NUMBER,
             last_name VARCHAR2,
             first_name VARCHAR2,
             email VARCHAR2,
             phone_number VARCHAR2,
             hire_date DATE,
             job_id VARCHAR2,
             salary NUMBER,
             commission_pct NUMBER,
             manager_id NUMBER,
             department_id NUMBER) IS
           BEGIN
             INSERT INTO employees VALUES (employee_id,
               last_name,
               first_name,
               email,
               phone_number,
               hire_date,
               job_id,
               salary,
               commission_pct,
               manager_id,
               department_id);
           END hire_employee;
         -- Code for procedure remove_employee:
           PROCEDURE remove_employee (emp_id NUMBER) IS
           BEGIN
             DELETE FROM employees WHERE employee_id = emp_id;
           END remove_employee;
         -- Code for function num_above_salary:
           FUNCTION num_above_salary (emp_id NUMBER) RETURN NUMBER IS
             emp_sal NUMBER(8,2);
             num_count NUMBER;
           BEGIN
             SELECT salary INTO emp_sal FROM employees
             WHERE employee_id = emp_id;
             SELECT COUNT(*) INTO num_count FROM employees
             WHERE salary > emp_sal;
             RETURN num_count;
           END num_above_salary;
         END emp_actions;
         /
 
Package body created.
 
Command> BEGIN
         /* call function to return count of employees with salary
            greater than salary of employee with employee_id = 120
         */
           DBMS_OUTPUT.PUT_LINE
             ('Number of employees with higher salary: ' ||
               TO_CHAR(emp_actions.num_above_salary(120)));
         END;
         /
Number of employees with higher salary: 33
 
PL/SQL procedure successfully completed.

Verify the count of 33.

Command> SELECT salary FROM employees WHERE employee_id = 120;
< 8000 >
1 row found.

Command> SELECT COUNT (*) FROM employees WHERE salary > 8000;
< 33 >
1 row found.

Now add an employee and verify results. Then, remove the employee and verify that the employee was deleted from the employees table.

Command> BEGIN 
           emp_actions.hire_employee(300,
             'Belden',
             'Enrique',
             'EBELDEN',
             '555.111.2222',
             '31-AUG-04',
             'AC_MGR',
             9000,
             .1,
             101,
             110);
         END;
         /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM employees WHERE employee_id = 300;
< 300, Belden, Enrique, EBELDEN, 555.111.2222, 2004-08-31 00:00:00, AC_MGR, 9000, 
.1, 101, 110 >
1 row found.
Command> BEGIN
           emp_actions.remove_employee (300);
         END;
         /
 
PL/SQL procedure successfully completed.
 
Command> SELECT * FROM employees WHERE employee_id = 300;
0 rows found.

Using Synonyms for Packages

TimesTen supports private and public synonyms (aliases) for database objects, including PL/SQL procedures, functions, and packages. Synonyms are often used to mask object names and object owners or to simplify SQL statements.

To create a private synonym for package foopkg in your schema:

CREATE SYNONYM synfoopkg FOR foopkg;

To create a public synonym for foopkg:

CREATE PUBLIC SYNONYM pubfoopkg FOR foopkg;

Also see Using Synonyms for Procedures and Functions in this document and Privileges for PL/SQL Statements and Operations in Oracle TimesTen In-Memory Database Security Guide.

Note:

You cannot create synonyms for individual member subprograms of a package.

This is valid:

create or replace public synonym pubtestpkg for testpkg;

This is not valid:

create or replace public synonym pubtestproc for testpkg.testproc;