279-285

Previous

Table of Contents

Next

The WaitOne() Procedure

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).

Using Signals

Let s 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.

DBMS_DDL

The DBMS_DDL package contains only two procedures ” Alter_Compile() and Analyze_Object() ” neither of which performs traditional DDL commands like CREATE TABLE :

   The Alter_Compile() procedure is used to recompile procedures, functions, packages, and package bodies.

   The Analyze_Object() procedure is used to calculate statistics for use by the cost-based optimizer.

Each of these procedures is described in the following sections, with definitions of the types and numbers of parameters.

The Alter_Compile() Procedure

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

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:

    type ”Indicates the type of the object for which statistics will be generated. This must be ˜CLUSTER , ˜INDEX , or ˜TABLE .

    schema ”Indicates the owner of the specified object.

    name ”Indicates the name of the object.

The remaining parameters instruct the procedure about how to generate statistics for the object:

    method ”Must either be NULL or contain the string ˜ESTIMATE . If the parameter isn t NULL , then one of the next two parameters must contain a value that indicates the sampling to be performed so that statistics can be estimated for the object. If the parameter is NULL , statistics will be computed.

    estimate_rows ”Specifies the number of rows from the object that should be used to estimate statistics for the object.

    estimate_percent ”Specifies the percentage of rows from the object that should be used to estimate statistics.

Previous

Table of Contents

Next