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.