| Previous | Table of Contents | Next |
by Ivan Oss
Any Oracle application developer or DBA knows how useful PL/SQL is for combining DML statements with the features of a procedural language. PL/SQL is a powerful language for building transactional applications, but how do you perform operations like scheduling jobs or writing files to the operating system or creating objects? The answer is, of course, the packages provided with PL/SQL. These packages allow you to perform a variety of useful tasks that extend the functionality of the language. Among others, these tasks include:
These packages extend the basic features of PL/SQL and make it a powerful language for developing database applications. There are also packages designed to address other areas of functionality, such as remote procedure calls and user-defined locking. The packages covered here are among the most useful and commonly applied. Application developers and DBAs should familiarize themselves with these packages in order to get the most out of their Oracle databases.
In PL/SQL 8.0, some new packages are introduced that address issues such as more robust job queuing and the reading and writing of LOBs. These packages will be discussed in detail, along with some of the more important standard packages of earlier PL/SQL releases.
I have grouped the packages by the tasks they perform. Hopefully this format will make them easy to find when you know what you need to accomplish, but don’t know which package to use. This chapter provides quick references for packages’ syntax and a basic conceptual overview of their use.
The supplied packages are stored in two files: the package headers and the package bodies. The package bodies are in a wrapped format that is unreadable, but the package headers are a useful source of information because they contain programmer comments and parameter requirements. The location of these files depends on the operating system. On UNIX systems, they are in $ORACLE_HOME/rdbms/admin and are created by running catproc.sql as SYS from that same directory. The user of the packages must have EXECUTE permission or be connected as SYS. There are public synonyms for the packages, so it’s not necessary to prefix their names with SYS when calling them.
The following is a syntactical summary of packages that address the major areas of functionality. They are arranged by their function, and there are brief examples of their use.
Applications developed for in PL/SQL usually retrieve and store data in the database itself, and interaction with the operating system is limited to the database’s physical storage activities. One might think there is no way to access the database by any other means than SQL statements, but there are other ways. Imagine writing a sentence like “Initiate backup sequence” to a file on the operating system and having the database respond by running all the necessary steps for a hot backup. Such a task would be easy if you were using the UTL_FILE package. The database engine becomes a freeform writer and reader of data generated by all manner of processes through this simple interface.
UTL_FILE
This package allows you to write to and read from files on the operating system. You might want to write to the operating system when logging events or creating reports. You could also use it as a debugging tool. UTL_FILE’s counterpart TEXT_IO is used for client-side I/O.
To use UTL_FILE, the operating system user that the Oracle RDBMS is running under must have the appropriate rights at the operating system level because this is the user that performs all reads and writes. Also, the directory being written to must be registered in the init.ora file. Several directories can be registered:
UTL_FILE_DIR = /u01/app/oracle/logs UTL_FILE_DIR = /u01/app/oracle/admin/prod/bdump UTL_FILE_DIR = *
Note:
TEXT_IO enforces no database-level security. All of the following procedures apply to TEXT_IO as well.
FUNCTION FOPEN(
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2)
RETURN FILE_TYPE;
| Previous | Table of Contents | Next |