PL/SQL User's Guide and Reference
Product-specific Packages
Oracle7 and various Oracle tools are supplied with product-specific packages that help you build PL/SQL-based applications. For example, Oracle7 is supplied with the packages DBMS_STANDARD, DBMS_SQL, DBMS_ALERT, DBMS_OUTPUT, DBMS_PIPE, UTL_FILE, and others. Brief descriptions of these packages follow; for more information, see Oracle7 Server Application Developer's Guide.
DBMS_STANDARD
Package DBMS_STANDARD provides language facilities that help your application interact with Oracle. For instance, a procedure named raise_application_error lets you issue user-defined error messages. That way, you can report errors to an application and avoid returning unhandled exceptions. For an example, see "Using raise_application_ error" ![[*]](jump.gif)
.
DBMS_SQL
Package DBMS_SQL allows PL/SQL to execute SQL data definition and data manipulation statements dynamically at run time. For an example, see "Using DDL and Dynamic SQL" .
DBMS_ALERT
Package DBMS_ALERT lets you use database triggers to alert an application when specific database values change. The alerts are transaction based and asynchronous (that is, they operate independently of any timing mechanism). For example, a company might use this package to update the value of its investment portfolio as new stock and bond quotes arrive.
DBMS_OUTPUT
Package DBMS_OUTPUT enables you to display output from PL/SQL blocks and subprograms, which makes it easier to test and debug them. The put_line procedure outputs information to a buffer in the SGA. You display the information by calling the procedure get_line or by using the command SET SERVEROUTPUT ON in SQL*Plus or Server Manager. For an example, see "Displaying Output" .
DBMS_PIPE
Package DBMS_PIPE allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures pack_message and send_message to pack a message into a pipe, then send it to another session in the same instance.
At the other end of the pipe, you can use the procedures receive_message and unpack_message to receive and unpack (read) the message. Named pipes are useful in many ways. For example, you can write routines in C that allow external servers to collect information, then send it through pipes to procedures stored in an Oracle database.
UTL_FILE
Package UTL_FILE allows your PL/SQL programs to read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations.
When you want to read or write a text file, you call the function fopen, which returns a file handle for use in subsequent procedure calls. For example, the procedure put_line writes a text string and line terminator to an open file. The procedure get_line reads a line of text from an open file into an output buffer.
PL/SQL file I/O is available on both the client and server sides. However, on the server side, file access is restricted to those directories explicitly listed in the accessible directories list, which is stored in the Oracle initialization file.