12 Built-In Packages

How do I…

12.1 Execute dynamic SQL with DBMS_SQL?

12.2 Submit scheduled jobs with DBMS_JOB?

12.3 Communicate between sessions with DBMS_PIPE?

12.4 Monitor the database asynchronously with DBMS_ALERT?

12.5 Read and write operating system files with UTL_FILE?

12.6 Use comma separated lists with DBMS_UTILITY?

12.7 Manage large objects with DBMS_LOB?

Built-in packages are provided by Oracle to extend the functionality of the database. Oracle’s modular approach allows new features to be added while maintaining compatibility with existing versions. Built-in packages contain functions and procedures that perform many tasks that would otherwise be impossible, or at least very difficult, to execute. With each new release, Oracle extends the functionality of the database by including new built-in packages. Many of the How-To’s presented in this chapter require PL/SQL versions 2.1, 2.2, 2.3, or 3.0. The PL/SQL version required to perform each one is provided at the beginning of the How-To.

12.1 Execute Dynamic SQL with DBMS_SQL

The ability to execute dynamic SQL statements within PL/SQL greatly extends the tasks you can perform. The DBMS_SQL package, available in PL/SQL version 2.1 and higher, enables you to create, parse, and send a SQL statement to the database at runtime. This enables you to build SQL statements based on the results of actions or queries performed at runtime. This How-To presents the technique used to execute dynamic SQL statements.

12.2 Submit Scheduled Jobs with DBMS_JOB

It is likely that within one or more of your applications, you need to perform some process at a regularly scheduled interval or need to schedule a program to be executed later. The DBMS_JOB package, available in PL/SQL version 2.2 and higher, enables you to schedule a single or recurring job. This How-To explores the use of the DBMS_JOB package to schedule programs within Oracle.

12.3 Communicate Between Sessions with DBMS_PIPE

Messages can be sent between Oracle processes by using the DBMS_PIPE package. Named pipes referenced by one or more processes can be created in the database. This How-To presents the method used to pass messages between processes using DBMS_PIPE.

12.4 Monitor the Database Asynchronously with DBMS_ALERT

It is sometimes necessary to perform an action when an event occurs in the database or to broadcast an event to other processes. The DBMS_ALERT package allows events to be recognized and their notification to occur asynchronously within the database. This How-To explores using the DBMS_ALERT package within Oracle.

12.5 Read and Write Operating System Files with UTL_FILE

The UTL_FILE package, available in PL/SQL version 2.3 and higher, enables you to read and write operating system files through PL/SQL. Files can be written on the server system by creating stored procedures or functions that use the UTL_FILE package. Files can also be written on the client system by using the UTL_FILE package in client-side PL/SQL modules.

12.6 Use Comma Separated Lists with DBMS_UTILITY

Comma separated lists can be moved in and out of PL/SQL tables using procedures in the DBMS_UTILITY package. The COMMA_TO_TABLE procedure converts a comma separated list into a PL/SQL table, and the TABLE_TO_COMMA procedure converts a PL/SQL table into a comma separated list. This How-To presents the method used to work with comma separated lists in PL/SQL.

12.7 Manage large objects with DBMS_LOB

Oracle8 supports the definition, creation, deletion, and updates of LOBs. The DBMS_LOB package allows piecewise random access to LOB data. Functions and procedures in the DBMS_LOB package allow read and write operations on Oracle Large Object (LOBs) datatypes: BLOB, CLOB and NCLOB as well as read-only access to BFILEs. This How-To presents techniques to manage LOBs.