This chapter describes the meaning and the structure of a package.
A package is a schema object that contains one or more tbPSM procedures or functions. The schema objects contained in a package include variables, cursors, and exceptions as well as procedures and functions.
Like procedures and functions, a package can be used in any SQL statement.
The following are the benefits of using a package:
Easy to develop and manage the package procedures and functions.
For instance, a variable which is commonly accessed by multiple procedures and functions can be contained in a package.
Easy to develop applications.
Various functions contained in a package help develop more powerful applications.
Efficient in execution.
Granting and withdrawal of permissions, memory loading, etc. are carried out by each package, thereby enabling efficient execution of applications.
Packages consist of user-defined packages and system packages, which are provided by Tibero.
A user defined package is a package that has been defined by a user for a specific purpose.
Like procedures and functions, a user-defined package can be defined as follows:
An external interface that contains public procedures, functions, variables, and constants.
The details of package declarations are as follows:
Syntax
CREATE [OR REPLACE] PACKAGE package_name [invoker_rights] AS -- Declaration of public procedures -- Declaration of public functions -- Declaration of public variables, constants, and exceptions END [package_name];
Features
The CREATE PACKAGE statement is used to create a declaration.
After package_name, an option should be specified to execute the package using either invoker's rights or definer's rights. If omitted, the package defaults to definer's rights.
The package name can be optionally specified after END.
Examples
[Example 1.1] Declaration of EMP_MGMT Package
CREATE PACKAGE EMP_MGMT AS ... Declaration part of EMP_MGMT package ...
FUNCTION HIRE(ENAME VARCHAR, ADDR VARCHAR, SALARY NUMBER,
DEPTNO INT) RETURN INT;
PROCEDURE FIRE(EMPNO INT);
PROCEDURE RAISE_SALARY(EMPNO INT, AMOUNT NUMBER);
EMP_NOT_FOUND EXCEPTION;
END EMP_MGMT;
In the above example, the EMP_MGMT package is executed using definer's rights, and contains two public procedures, a function, and a public exception.
The package body implements actual programs. This includes private procedures, functions, and variables, as well as programs such as public procedures and functions.
The details of a package body are as follows:
Syntax
CREATE [OR REPLACE] PACKAGE BODY package_name AS -- Implementation of public procedures -- Implementation of public functions -- Declaration of private procedures and functions -- Implementation of private procedures and functions -- Definition of private variables, constants and exceptions [BEGIN -- Set of initialization commands END [package_name];
Features
The CREATE PACKAGE BODY statement is used to create a package body.
The package name can be optionally specified after END.
Examples
[Example 1.2] Body of EMP_MGMT Package
CREATE PACKAGE BODY EMP_MGMT AS ... Body of EMP_MGMT Package ... NUM_EMP INT; EMPNO NUMBER := 0; FUNCTION HIRE(ENAME VARCHAR, ADDR VARCHAR, SALARY NUMBER, DEPTNO INT) RETURN INT AS BEGIN EMPNO := EMPNO + 1; ... Creating an EMPNO ... INSERT INTO EMP VALUES (EMPNO, ENAME, ADDR, SALARY, DEPTNO); NUM_EMP := NUM_EMP + 1; RETURN(EMPNO); END; PROCEDURE FIRE(EMPNO INT) AS BEGIN DELETE FROM EMP WHERE EMP.EMPNO = FIRE.EMPNO; NUM_EMP := NUM_EMP ? 1; END; PROCEDURE RAISE_SALARY(EMPNO INT, AMOUNT NUMBER) AS BEGIN UPDATE EMP SET SALARY = SALARY + AMOUNT WHERE EMP.EMPNO = RAISE_SALARY.EMPNO; END; END EMP_MGMT;
The above example contains the definitions of all procedures and functions included in [Example 1.1] and declares a private variable, NUM_EMP.
Defining a package procedure or function is done in the same way as defining a general procedure or function except for the package variables, constants, and exceptions.
When the column and a variable have the same name in a procedure or a function, avoid confusion by specifying the name of the table which contains the column, procedure, or function. For example, the column EMPNO contained in table EMP and the variable EMPNO transferred to procedure FIRE have the same name, so the table name and the procedure name should be specified.
In addition to the package owner, other users can also invoke procedures and functions included in the package. To invoke a procedure or a function, the user must be granted EXECUTE permission for the package. The package owner can always invoke the package.
If a view is defined using package procedures or functions, a user must be granted SELECT permission to access to the view. In this case, EXECUTE permission for the package is not required. To reference a package procedure or a function, the name of the package or the function name should be specified with the package name.
The following is an example of invoking the HIRE function within the EMP_MGMT package specified in [Example 1.1]. In this example, the return value is stored in the EMPNO variable.
DECLARE
empno NUMBER;
BEGIN
empno := EMP_MGMT.HIRE('John', 'New York', 45000, 5);
DBMS_OUTPUT.PUT_LINE('Hired employee No. is ' || empno);
END;
A system package is a package library that is provided with Tibero by default. It is owned by the SYS user. A public synonym with the same name as the system package is also defined so that general users can use the package afterTibero is installed. If a user invokes a procedure or a function in the system package, the procedure or the function is executed using invoker's rights.
System packages provide general users with various extended functions. System packages can be directly used in SQL statements and used for defining other procedures, functions, and packages.
Calling procedures and functions in a system package is the same as in a user-defined package.
The following is an example of displaying the name and the creator of large objects with a size of over 32 kilobytes:
SELECT name, creator FROM image WHERE DBMS_LOB.GETLENGTH(data) >= 32768;
In the above example, data is a large object column in the image table.
The following table shows the list of system packages provided by Tibero.
| System Package | Description |
|---|---|
| ANYDATA | Holds any data type. |
| DBMS_ALERT | Provides procedures for notification and waiting. They are integrated with a Tibero database. |
| DBMS_APPLICATION_INFO | Modifies a value of a view related to application information. |
| DBMS_AQ | Provides procedures and functions related to Advanced Queuing. |
| DBMS_AQADM | Provides procedures and functions used to configure and manage Advanced Queuing. |
| DBMS_CRYPTO | Encrypts or decrypts data using DES, DES3, and AES algorithms, and chain and padding methods. |
| DBMS_DB_VERSION | Checks for product version information. |
| DBMS_DDL | Provides WRAPPING and other DDL related functions for DDLs that start with CREATE OR REPLACE PROCEDURE, or PACKAGE. |
| DBMS_DEBUG | Debugs PSM programs using two sessions. |
| DBMS_ERRLOG | Supports the error logging function for DML. |
| DBMS_FLASHBACK | Provides the flashback function. |
| DBMS_JAVA | Accesses Java objects used in the database. |
| DBMS_JOB | Manages JOBs. |
| DBMS_JOB_WITH_NAME | Manages JOBs using their names. |
| DBMS_LOB | Processes BLOB and CLOB data. |
| DBMS_LOCK | Sets a session to standby. |
| DBMS_METADATA | Provides a function for creating a DDL creation script by querying DB object metadata. |
| DBMS_MONITOR | For monitoring SQL executions by instance, session, and client ID. |
| DBMS_MVIEW | Provides information about materialized views and refreshes them. |
| DBMS_OBFUSCATION_TOOLKIT | Encrypts or decrypts data using the DES and DES3 algorithms. |
| DBMS_OUTPUT | Stores and retrieves messages in/from a message buffer. |
| DBMS_PIPE | Supports communication between sessions on the same instance. |
| DBMS_RANDOM | Creates a number or a string. |
| DBMS_REDEFINITION | Redefines a specific table in an online state. |
| DBMS_REPAIR | Examines and recovers a broken block in a table or an index. |
| DBMS_RESOURCE_MANAGER | Allocates resources according to task characteristics. |
| DBMS_RESULT_CACHE | Manages the result cache. |
| DBMS_RLS | Manages a virtual private database. |
| DBMS_ROWID | Views or creates information in a ROWID. |
| DBMS_SCHEDULER | Schedules and manages PSM programs. |
| DBMS_SESSION | Manages session identifiers. |
| DBMS_SPACE | Provides information about materialized views and refreshes them. |
| DBMS_SPACE_ADMIN | Administers segments. |
| DBMS_SPH | Provides functions required for using SQL Plan History (hereafter SPH). |
| DBMS_SQL | Uses dynamic SQL to access the database. |
| DBMS_SQLTUNE | Creates real-time SQL monitoring reports. |
| DBMS_SQL_TRANSLATOR | Creates and manages SQL translation profiles. |
| DBMS_STATS | Manages statistical information about database objects. |
| DBMS_SYSTEM | Used by the SYS user to control the system. |
| DBMS_TPR | Provides functions required for using Tibero Performance Repository (TPR). |
| DBMS_TRANSACTION | Executes a transaction statement, and administers the transactions. |
| DBMS_TYPES | Defines data types as numbers. |
| DBMS_UTILITY | Provides various utility functions. |
| DBMS_VERIFY | Checks integrity of various items. |
| DBMS_XMLDOM | Provides DOM API for XML document processing. |
| DBMS_XMLGEN | Receives a query and creates an XMLTYPE result set for the query. |
| DBMS_XPLAN | Queries information about SQL plans and executions. |
| HTF | Creates HTML tags. |
| HTP | Creates HTML tags. Linked with mod_tbpsm developed with Apache Web server modules, it creates an HTML page when invoking a procedure. |
| OWA_UTIL | Includes the utility usually used by a web agent. |
| TEXT_DDL | Changes TEXT INDEX settings. |
| UTL_COMPRESS | For data compression/decompression. |
| UTL_ENCODE | Uses standard encoding technology to transfer data between hosts. |
| UTL_FILE | Accesses files managed by the operating system. |
| UTL_HTTP | Makes web page or service calls based on the web standard (RFC2616). |
| UTL_I18N | Provides compatibility functions for language and international settings. |
| UTL_MATCH | Provides string comparison functions. |
| UTL_RAW | Processes RAW data. |
| UTL_RECOMP | Recompiles invalid objects. |
| UTL_TCP | Supports TCP/IP socket communication with a remote TCP server using tbPSM. |
| UTL_URL | Converts a URL (Uniform Resource Locator) address to an escaped format. |
For more details about each package, refer to the relevant chapter.