Reviewing Oracle-supplied packages

Oracle-supplied packages are provided by the Oracle server and inbuilt in the database as a wrapper code. These packages not only facilitate the database programmers to work on extended functionalities but also reduce writing extensive and complex code. Use of Oracle-supplied API is always recommended as it improves the code standardization, too.

The scripts for these packages are available in the $ORACLE_HOME\RDBMS\ADMIN\ folder. All packages reside on the database server. Public synonyms are available for these packages so that these packages are accessible to all users. Until Oracle 11g, more than 1000 packages were available and this count tends to increase with every database release.

Some of the important packages are listed as follows:

• DBMS_ALERT: This package is used for notification of database events

• DBMS_LOCK: This package is used for managing the lock operations (lock, conversion, release) in PL/SQL applications

• DBMS_SESSION: This package is used to set session level preferences from PL/SQL programs (similar to ALTER SESSION)

• DBMS_OUTPUT: This package is one of the most frequently used built ins for buffering of data messages and display debug information

• DBMS_HTTP: This package is used for HTTP callouts

• UTL_FILE: This package is used for reading, writing and other file operations on the server

• UTL_MAIL: This package is used to compose and send mails

• DBMS_SCHEDULER: This package is used for scheduling execution of stored procedures at a given time

Based on the objective achieved, the packages can be categorized as follows:

• Standard application development: DBMS_OUTPUT is the most frequently used package to display the required text. It is used for tracing and debugging purposes. Accessing and writing OS files was made possible through UTL_FILE. Similarly, system dependent binary files are accessed through the DBMS_LOB package.

The Oracle supplied packages often try to access SQL features which is their other big advantage.

• General usage and application administration: The Oracle server has many packages to monitor the applications and users. Stats generation, load history, and space management are the key objectives accomplished by these packages

• Internal support packages: Oracle maintains these packages for its own use.

• Transaction processing packages: Oracle provides utility packages which enables the monitoring of transaction stages. Though they are rarely used, but could efficiently ensure transparent and smooth transactions. For example, DBMS_TRANSACTION.

Among these categories, standard application development packages are the most frequently used ones.