|Previous||Table of Contents||Next|
The WaitOne() procedure is used by a PL/SQL block to wait for a particular signal to occur. The procedure has four parameters: name, message, status, and timeout.
PROCEDURE WaitOne (name IN varchar2, message OUT varchar2, status OUT integer, timeout IN number DEFAULT MAXWAIT)
The name parameter is the name of the signal for which the PL/SQL block is waiting. The message parameter returns any text that is passed with the signal when it occurs. The status parameter returns 0 if the signal was received or 1 if the procedure timed out while waiting for the signal. The timeout parameter indicates the interval (in seconds) that the WaitOne() procedure will wait for the specified signal. If no value for this parameter is specified, it defaults to the value of the DBMS_Alert.MAXWAIT constant. The value of this constant is 1,000 days (86,400,000 seconds).
Lets look at an example of some code that uses signals for interprocess communication. Presume we have an order entry system that must be tied to a legacy system. Data is input into the legacy system via a Pro*C program. The trigger in Listing 9.1 is implemented on the ORDERS table.
Listing 9.1 Using a trigger to send a signal.
TRIGGER ORDERS ARIU AFTER INSERT OR UPDATE ON ORDERS FOR EACH ROW CHANGED_ORDER_SIGNAL CONSTANT varchar2 (10) := 'Changed order'; NEW_ORDER_SIGNAL CONSTANT varchar2 (10) := 'New order'; BEGIN IF INSERTING THEN DBMS_Alert.Signal (NEW_ORDER_SIGNAL, 'A new order has been submitted.'); ELSIF UPDATING THEN DBMS_Alert.Signal (CHANGED_ORDER_SIGNAL, 'An order has been changed.'); END IF; END ORDERS_ARIU;
The Pro*C program is initiated by the system whenever the order entry form is run. This code makes a call to the DBMS_Alert.Register() procedure, as follows:
DBMS_ALERT.Register ('New order')
After registering for the signal, the Pro*C program goes into a loop. During each loop cycle, the program calls the DBMS_Alert.WaitOne() procedure:
DBMS_Alert.WaitOne (name => 'New order', message => alert_message, status => alert_return_value, timeout => 1);
This instructs the WaitOne() procedure to wait for one second. If no alert has occurred before the end of that second, the procedure returns a value of 1 for the status parameter.
Figure 9.2 illustrates how this implementation works.
Figure 9.2 Using the DBMS_Alert package.
The DBMS_DDL package contains only two proceduresAlter_Compile() and Analyze_Object() neither of which performs traditional DDL commands like CREATE TABLE:
Each of these procedures is described in the following sections, with definitions of the types and numbers of parameters.
The Alter_Compile() procedure is called to recompile a specific stored PL/SQL object. The procedure accepts three parameters: type, schema, and name.
PROCEDURE Alter_Compile (type IN varchar2, schema IN varchar2, name IN varchar2)
The type parameter indicates if the object is a procedure, function, package body, or package spec. The schema parameter indicates the name of the schema that owns the specified object. The name parameter is the name of the object that should be recompiled.
The Analyze_Object() procedure is called to estimate or calculate statistics for a table, cluster, or index. The statistics generated are used by the cost-based opti-mizer to determine the optimal execution path for DML statements. The procedure accepts six parameters: type, schema, name, method, estimate_rows, and estimate_percent.
PROCEDURE Analyze_Object (type IN varchar2, schema IN varchar2, name IN varchar2, method IN varchar2, estimate_rows IN number := NULL, estimate_percent IN number := NULL)
The first three parameters identify the object for which statistics will be generated:
The remaining parameters instruct the procedure about how to generate statistics for the object:
|Previous||Table of Contents||Next|