0146-0148

Previous Table of Contents Next

Page 146

Datatypes specified as parameters or return values for external functions are mapped to OCI C types. Given the PL/SQL definition in Listing 7.6, the C prototype might look like this:

 float calc_score(int val1, int val2, int val3); 

The PL/SQL function created in Listing 7.6 can be called by other program units as if it were implemented entirely in PL/SQL. When invoked, Oracle spawns a new process, extproc, which handles interprocess communications between PL/SQL and the host language. extproc remains active for the rest of the session. This listener receives the library name and call information from PL/SQL and passes it to extproc. The session-specific extproc process loads the shared library, executes the external procedure, and returns any output parameter data, exception information, or return values to PL/SQL via the listener.

NOTE

At this time, you should note two important points.
The first point is that a dedicated listener process must be available to handle external function calls. This listener must be configured in tnsnames.ora and listener.ora. The protocol should be specified as IPC, and in listener.ora, the PROGRAM_NAME must be extproc.
The second point is related to the actual implementation of the shared library. This is probably an obvious point, but any functions or procedures that will be accessed by Oracle must be thread-safe. Access to shared resources, such as static data or files, must be synchronized properly.

External procedures are a powerful tool; they provide the means to extend the capabilities of PL/SQL. External procedures also can provide improved performance for complex calculations or other resource- intensive tasks. Because they can be called from any PL/SQL program unit (including triggers), implement callbacks, and perform any tasks allowable under the operating system, the possible uses of external procedures are almost unlimited. Users familiar with the DBMS_PIPE package will recognize that these capabilities are not entirely new, but the Oracle8 implementation is vastly improved.

Advanced Queuing

A second powerful new feature of Oracle8 that is accessed through PL/SQL packages is advanced queuing (or Oracle/AQ). This is more than an extension of the Oracle job queue. Oracle/AQ furnishes the messaging, scheduling, and management capabilities that typically are reserved for proprietary messaging middleware and transaction-processing monitors .

Oracle/AQ messages are stored in tables and provide built-in persistence and recovery capabilities. These messages are implemented as object types. Custom application-specific queues are created to handle message- and object-specific data. Messages are added to queues by using

Page 147

the DBMS_AQ.ENQUEUE procedure. The parameters of this procedure enable users to specify the following:

• The destination queue
• Application-specific type and identifying information for the message
• A priority
• Scheduling information
• Transactional and custom ordering information
• Message parameters
• A destination queue for exception information
• A destination queue for the reply message

A single output parameter supplies the caller with a globally unique message identifier.

Messages are removed from a queue by using the DBMS_AQ.DEQUEUE procedure. This procedure retrieves the message information supplied by the sender's call to DBMS_AQ.ENQUEUE. If no custom ordering was specified, first in, first out (FIFO) ordering is assumed. Input parameters are supplied to specify the following:

• The queue to search
• Application-specific type and identifying information for the message to be read
• Amount of time to wait for a message if none exists
• Transactional information
• Dequeue behavior

DBMS_AQ.DEQUEUE can specify that the message should be destroyed after it is read, that it should be left in the queue, or that it should be locked and left in the queue. The sender of a message can attempt to cancel it by dequeuing it or by sending an application-defined cancel message.

A number of factors determine the order in which messages are read from the queue, including message priority and correlation information. Note that the application calling DBMS_AQ.DEQUEUE must supply the same object type to retrieve message parameters as the caller supplied. The process of queuing and dequeuing can be used to perform complex transactions. The queuing application can specify that a message is part of an ongoing transaction by suppyling the transactional parameter and setting it to true. It is up to the dequeuing application to determine the proper ordering of messages that participate in a single transaction. The corr_id and user_data parameters, which are used to supply application-specific data as a VARCHAR2 or an object, respectively, can be used to provide ordering information for transactions that involve multiple messages. A more general (but also more complex and error-prone ) approach to ordering messages within a transaction could be devised using the priority, delay, seq_deviation, and relative_msgid parameters.

Page 148

The DBMS_AQADM package contains the functions and procedures for managing queues. This package enables administrators to perform the following tasks:

• Create new queue tables: Different queue tables are created to partition an application, impose security restrictions, or deal with different message types. All messages are stored in queue tables.
• Create new queues: A named queue is associated with a table, and parameters are used to specify the type of queue (normal or exception), retry behavior, and message-retention behavior.
• Alter a queue: Modify a queue's type and behavior.
• Start a queue: Queues must be started before they can be accessed.
• Stop a queue: Queues can be stopped to halt processing for database maintenance, backups , and so on.
• Drop a queue: Drops a named queue.
• Drop a queue table: Removes the queue table and all messages that it contains.

A number of new System views are provided to monitor queue activities. These views provide information on the contents of the queue tables, queue parameters, and the ordering attributes of queues. The database administrator (DBA) versions of these views also provide timing information, user IDs of senders and receivers, and so on.

TIP

Because messages and replies are object types, they can contain almost anything. A message could start a batch process or request a complex query. The reply could contain a simple acknowledgment or a collection type (making up a result set). Oracle/AQ is completely flexible in this regard.

DBMS_LOB Package

The capability to store multiple LOB columns in a single table is new in Oracle8; it provides improved performance and storage flexibility. The new LOB types and features are useful particularly for multimedia applications that integrate large amounts text, audio, and video.

The Oracle8 DBMS_LOB package provides the methods for manipulating LOB types. As mentioned previously, Oracle8 defines four LOB_TYPES: BLOB, CLOB, NCLOB, and BFILE. Of these types, only BFILEs are stored outside the database, so a different set of DBMS_LOB routines applies to them. All DBMS_LOB procedures operate on locators. This means that internal LOB objects must exist in the database before you can use the DBMS_LOB package to manipulate them.

Previous Table of Contents Next