Skip Headers

Oracle® Application Server Reports Services Publishing Reports to the Web 10g (9.0.4) Part Number B13673-01 |
| Home | Contents | Index

# 17 Using Event-Driven Publishing

Modern business processes often require the blending of automation into the work environment through the invocation of behind-the-scenes functions and procedures. Behind-the-scenes tasks can include the automatic production of output such as an invoice that prints automatically when an order is processed, a Web site that is automatically updated with current data, or an automatic e-mail with fresh report output when a transaction is completed.

Automatic output in response to events used to be a fairly complicated effort, particularly if you wished to produce the same results possible through interactive, RAD development tools, such as Oracle Reports Developer.

To address the requirement of automatic output, Oracle introduced a scheduling mechanism in OracleAS Reports Services that enabled the invocation of reports on a scheduled basis without requiring additional user interaction. But this left one requirement unresolved: the ability to automatically run a report in response to an event in the database, such as the insertion of a record or the change of a value.

With the OracleAS Reports Services Event-Driven Publishing API, you can automatically run a report in response to an event in the database, such as the insertion of a record or the change of a value. The Event-Driven Publishing API is a PL/SQL API that allows for the automatic submission of jobs to OracleAS Reports Services from within the database.

This chapter provides a look at the Event-Driven Publishing API and includes examples of its use. It includes the following sections:

- The Event-Driven Publishing API

- Debugging Applications that Use the Event-Driven Publishing API

- Invoking a Report From a Database Event

- Integrating with Oracle Advanced Queuing

## 17.1 The Event-Driven Publishing API

The Event-Driven Publishing API is a PL/SQL package that provides the basic functions required for the development of procedures that respond to events in the database. Event-driven jobs are submitted using the HTTP protocol. The server assigns a unique `job_ident` record to every call, useful for tracking the status of the job.

### 17.1.1 Elements of the API

The API consists of several key elements:

- The SRW-Package contains all relevant functions and procedures for submitting jobs, checking job status, and cancelling jobs, as well as manipulating parameter lists.

- The ParamList-Type defines a parameter list. A parameter list is the main vehicle for passing values when submitting a job. A parameter list is required for each job submission. It must contain several key parameters.

- The ParamList-Object is required for such features as Advanced Queuing, where a parameter list must be stored in the database so that it may be passed along with a message.

These API elements are discussed in more detail in the following sections.

The API is installed together with OracleAS Reports Services Security and OracleAS Portal, but neither is required. Installation scripts are also available separately should you want to install the API into a database that does not also hold Oracle Portal:

- `srwAPIins.sql` installs the Events-Driven Publishing API.

- `srwAPIgrant.sql` grants access privileges to the API. Run this script for each user to whom you will grant access to the API. If everyone may have access, you can run this once and grant access to PUBLIC.

- `srwAPIdrop.sql` removes the API.

### 17.1.2 Creating and Manipulating a Parameter List

A parameter list is a PL/SQL variable of type `SRW_PARAMLIST`. A variable of this type is an array of 255 elements of type `SRW_PARAMETER`, which itself consists of two attributes: NAME and VALUE. The API provides procedures for manipulating parameter lists, including:

- Add_Parameter

- Remove_Parameter

- Clear_Parameter_List

These procedures allow you to manipulate your parameter lists. They are discussed briefly in this section. You'll find more information in the Oracle Reports API documentation.

Note: Look for upcoming information about Reports APIs on the Oracle Technology Network, (`http://otn.oracle.com`).

#### 17.1.2.1 Add_Parameter

Whenever you use a parameter list for the first time, it must be initialized before you can add parameters to it. For example:

    DECLARE

    myPlist SRW_PARAMLIST;

    BEGIN

    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    srw.add_parameter(myPlist,'myParameter','myValue');

    END;

Both attributes of a parameter (NAME and VALUE) are of type VARCHAR2 and may not exceed a length of 80 characters for the NAME and 255 characters for the value.

The `ADD_PARAMETER` function has a third—optional—attribute, called `MODE`. `MODE` determines whether a parameter will be overwritten or an error raised in the event that a parameter with the same name already exists. To specify that an error will be raised in the event of duplicate names, use the constant `CHECK_FOR_EXISTANCE`. This is the default value for the `MODE` attribute. To specify that a parameter will be overwritten in the event of duplicate names, use the constant `OVERWRITE_IF_EXISTS`.

#### 17.1.2.2 Remove_Parameter

Use `REMOVE_PARAMETER` to remove a parameter from a parameter list. Call the procedure, and pass the parameter list from which you want to remove a parameter along with the name of the parameter you want to remove.

For example:

    DECLARE

    myPlist SRW_PARAMLIST;

    BEGIN

    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    srw.add_parameter(myPlist,'myParameter','myValue');
    srw.remove_parameter(myPlist,'myParameter');

    END;

#### 17.1.2.3 Clear_Parameter_List

To remove ALL parameters from your list, use `CLEAR_PARAMETER_LIST`. For example:

    DECLARE

    myPlist SRW_PARAMLIST;

    BEGIN

    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    srw.add_parameter(myPlist,'myParameter','myValue');
    srw.clear_parameter_list(myPlist);

    END;

This will remove all parameters from your list.

### 17.1.3 How to Submit a Job

A parameter list contains all vital parameters for submitting a job. The job type determines which parameters are required on the list to enable the Reports Server to process the request.

The listed parameters are the same ones that you must specify when you submit a job from a browser to the Reports Servlet. In such a case, if the job is a report you will need at least the following parameters but may have more:

- `GATEWAY` provides the URL to the Reports Servlet you will use to process the request.

- `SERVER` identifies the Reports Server to be used in conjunction with the servlet.

- `REPORT` identifies the report file to be run.

- `USERID` identifies the name and user ID of the person running the report.

- `AUTHID` provides authorization information in the event you are running against a secured server.

Each request returns a job_ident record that holds the information required to identify the job uniquely. This information is stored in variable of type `SRW.JOB_IDENT`. Be aware that this is a PACKAGE-TYPE and must be referenced `SRW.JOB_IDENT`; while the parameter list is an OBJECT-TYPE and must be referenced `SRW_PARAMLIST`.

For example:

     DECLARE

    myPlist SRW_PARAMLIST;
    myIdent SRW.Job_Ident;

     BEGIN

    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    srw.add_parameter(myPlist,'GATEWAY','http://…');
    srw.add_parameter(myPlist,'SERVER','mySVR');
    srw.add_parameter(myPlist,'REPORT','myReport.RDF');
    srw.add_parameter(myPlist,'USERID','me/secret');
    myIdent := srw.run_report(myPlist);

    END;

The API method `RUN_REPORT` takes a parameter list that contains all vital information as input (via `ADD_PARAMETER`), creates and submits the request, and returns the job_ident record.

The `job_ident` record contains the following parameters:

- `MyIdent.GatewayURL`

- `MyIdent.ServerName`

- `MyIdent.JobID`

- `MyIdent.AuthID`

These parameters are needed by the `SRW.REPORT_STATUS` function to get status information for a submitted job.

### 17.1.4 How to Check for Status

The Event-Driven Publishing API provides a two-way communication with the Reports Server. You submit a job to the server, and you can query the status of this job from the server using the `SRW.REPORT_STATUS` function.

This function will return a record of type `SRW.STATUS_RECORD` that holds the same information you would see in the job-status display if you were using the Reports Servlet's `SHOWJOBS` command.

For example:

    DECLARE

    myPlist SRW_PARAMLIST;
    myIdent SRW.Job_Ident;
    myStatus SRW.Status_Record;

    BEGIN

    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    srw.add_parameter(myPlist,'GATEWAY','http://…');
    srw.add_parameter(myPlist,'SERVER','mySVR');
    srw.add_parameter(myPlist,'REPORT','MyReport.RDF');
    srw.add_parameter(myPlist,'USERID','me/secret');
    myIdent := srw.run_report(myPlist);
    myStatus := srw.report_status(myIdent);

    END;

You can use the returned status record for fetching information about the status of your job.

### 17.1.5 Using the Servers' Status Record

The status record contains processing information about your job. It contains the same information found in the server queue (`SHOWJOBS`). Additionally, it contains information about the files produced for finished jobs and the lineage for scheduled jobs.

The most important information in the status record is the current job status and the status text, used in turn to check for runtime errors and their causes.

You can use timing information to determine if a job is subject to cancellation because it has exceeded its predicted time for completion.

One way to use the status record is to cancel a job. The Event-Driven Publishing API offers a method for cancelling a job that has been submitted to the server. This might be handy if you want to remove a job that has exceeded its allowed time to run or if you simply have scheduled jobs you want to cancel.

To cancel a job, use the following procedure:

    DECLARE

    myPlist SRW_PARAMLIST;
    myIdent SRW.JOB_IDENT;
    myStatus SRW.STATUS_RECORD;

    BEGIN

    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    SRW.ADD_PARAMETER(myPlist,'GATEWAY','http://…');
    SRW.ADD_PARAMETER(myPlist,'SERVER','mySVR');
    SRW.ADD_PARAMETER(myPlist,'REPORT','myReport.RDF');
    SRW.ADD_PARAMETER(myPlist,'USERID','me/secret');
    myIdent := SRW.RUN_REPORT(myPlist);
    myStatus := SRW.REPORT_STATUS(myIdent);
    if myStatus.StatusCode != srw.RUNNING then
    SRW.CANCEL_REPORT(myIdent);

    END;

As evident in this example, you cancel a report by calling the `CANCEL_REPORT` procedure (`SRW.CANCEL_REPORT`) and passing it the `job_ident` record of the job you want to cancel. The procedure takes an optional parameter list to enable you to pass any additional parameters you might need.

## 17.2 Debugging Applications that Use the Event-Driven Publishing API

Because these processes all run behind the scenes, there is no actual place where debugging information is produced during normal execution. Therefore, the API has two procedures that toggle a special debugging mode that produces extensive debugging information via DBMS_OUTPUT:

- `SRW.START_DEBUGGING`

- `SRW.STOP_DEBUGGING`

To switch on debugging mode simply call `SRW.START_DEBUGGING` and to stop it call `SRW.STOP_DEBUGGING`. The debugging-mode must be started immediately before you run your actual logic. It stays on as long as the current instance of the package is loaded.

One way you can display this information is by setting `SERVEROUT` to `ON` in SQL*PLUS before you run your script.

In addition to this method of debugging, the API has a set of pre-defined exceptions to be used for error handling. You'll find examples of these exceptions in the srw_test.sql script provided with your OracleAS Reports Services installation. Additionally, see the Oracle Reports API reference documentation for a detailed explanation of these exceptions.

Note: Look for upcoming information about Reports APIs on the Oracle Technology Network, (`http://otn.oracle.com`).

## 17.3 Invoking a Report From a Database Event

Database triggers are the primary mechanism for invoking reports using the Event-Driven Publishing API. The Oracle database allows you to define various scopes of triggers that fire in response to various events. To submit a database-driven job, you use the code described in the previous sections within a database trigger.

There are many ways to use event-driven publishing. One way is to create security protocols using a trigger that fires whenever a grant is done or a user logs on or off. Another way is to create automated processes that respond to certain types of changes to data in a table. For example, a database trigger could fire when the status of an expense report changes to DONE; in turn, a report could automatically be sent to an employee's manager.

For example:

    CREATE TRIGGER EXP_REP_TRG

    AFTER INSERT OR UPDATE on EXP_REP FOR EACH ROW
    myPlist SRW_PARAMLIST;
    myIdent SRW.JOB_IDENT;

    BEGIN

    IF (:new.ExpStat = 'DONE') THEN

    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    SRW.ADD_PARAMETER(myPlist,'GATEWAY','http://…');
    SRW.ADD_PARAMETER(myPlist,'SERVER','fooSVR');
    SRW.ADD_PARAMETER(myPlist,'REPORT','foo.RDF');
    SRW.ADD_PARAMETER(myPlist,'USERID','foo/bar');
    SRW.ADD_PARAMETER(myPlist,'ExpenseID',:new.ExpID);
    myIdent := SRW.RUN_REPORT(myPlist);

    END IF;

    END;

This trigger will fire after each update on the EXP_REP table. In the event the status changes to DONE, the report request is run.

If you want your request to run against a key specified in the cgicmd.dat file, specify the `CMDKEY` parameter in lieu of the `REPORT` parameter. If the key contains user ID information, you can omit the `USERID` parameter as well. For example:

    CREATE TRIGGER EXP_REP_TRG

    AFTER INSERT OR UPDATE on EXP_REP FOR EACH ROW
    myPlist SRW_PARAMLIST;
    myIdent SRW.JOB_IDENT;

    BEGIN

    IF (:new.ExpStat = 'DONE') THEN

    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    SRW.ADD_PARAMETER(myPlist,'GATEWAY','http://…');
    SRW.ADD_PARAMETER(myPlist,'SERVER','fooSVR');
    SRW.ADD_PARAMETER(myPlist,'CMDKEY','keyvalue');
    SRW.ADD_PARAMETER(myPlist,'ExpenseID',:new.ExpID);
    myIdent := SRW.RUN_REPORT(myPlist);

    END IF;

    END;

Additionally, if you have defined an advanced distribution model via a distribution XML file, you can specify that file with the `DESTINATION` parameter. For example:

    CREATE TRIGGER EXP_REP_TRG

    AFTER INSERT OR UPDATE on EXP_REP FOR EACH ROW
    myPlist SRW_PARAMLIST;
    myIdent SRW.JOB_IDENT;

    BEGIN

    IF (:new.ExpStat = 'DONE') THEN

    myPlist := SRW_PARAMLIST(SRW_PARAMETER('',''));
    SRW.ADD_PARAMETER(myPlist,'GATEWAY','http://…');
    SRW.ADD_PARAMETER(myPlist,'SERVER','fooSVR');
    SRW.ADD_PARAMETER(myPlist,'REPORT','foo.RDF');
    SRW.ADD_PARAMETER(myPlist,'USERID','foo/bar');
    SRW.ADD_PARAMETER(myPlist,'DISTRIBUTE','YES');
    SRW.ADD_PARAMETER(myPlist,'DESTINATION','filename.xml');
    SRW.ADD_PARAMETER(myPlist,'ExpenseID',:new.ExpID);
    myIdent := SRW.RUN_REPORT(myPlist);

    END IF;

    END;

This is one way to move this kind of logic from your application into the database and use the database as a central storage for business processes.

Note: You'll find additional examples of the Event-Driven Publishing API in action in the demo script `srw_test.sql`, included with your OracleAS Reports Services installation.

## 17.4 Integrating with Oracle Advanced Queuing

Oracle Advanced Queuing is a means for building an asynchronous request/response mechanism around a so-called queue and two processes: `ENQUEUE`, which puts `MESSAGES` into a queue, and `DEQUEUE`, which reads the queue.

Advanced queuing provides sophisticated mechanisms for distributing messages across queues and for queue subscription. These mechanisms are all built on top of these basic elements (`ENQUEUE`, `DEQUEUE`, and `MESSAGES`).

With the Event-Driven Publishing API you can use these queues to store and transmit report jobs. You can even build your own queuing mechanism if the one provided with OracleAS Reports Services does not fit your needs.

### 17.4.1 Creating a Queue That Holds Messages of Type SRW_PARAMLIST

A queue is a table in the database that holds, along with several administrative columns, an object column that represents a message. In our case the message is the parameter list.

The `dbms_AQadm` package, provided with Advanced Queuing, contains all the administrative functions required for setting up an advanced queuing system.

Use `dbms_AQadm.Create_Queue_Table` to create the physical table in the database. You must pass it a name for the table and a name for the object type that will define the message for this queue.

For example:

    …
    execute dbms_AQadm.Create_Queue_Table

    (queue_Table=>'queuename._tab',
    queue_Payload_Type=>'SRW_PARAMLIST_OBJECT',
    compatible=>'9.0');

In earlier examples, we created the object type `SRW_PARAMLIST_OBJECT` that encapsulates the `SRW_PARAMLIST` type in object notation so it can be used as a message.

After creating the queue table, you must create the queue with `dbms_AQadm.Create_Queue` and start the queue with `dbms_AQadm.Start_Queue`.

For example:

    …
    execute dbms_AQadm.Create_Queue

    (Queue_Name=>'queuename',Queue_Table=>'queuename._tab');

    prompt … starting queue
    execute dbms_AQadm.Start_Queue

    (Queue_Name=>'queuename');

    …

Note: You'll find a complete example for setting up, creating, and starting a simple queue in the demo file `srwAQsetup.sql`, included with your OracleAS Reports Services installation.

Having created and started the queue, what you need now is a procedure that creates a message in this queue and a procedure that reads out the queue and submits the job to the server. These are discussed in the following sections.

### 17.4.2 Creating the Enqueuing Procedure

The enqueuing procedure is responsible for putting a message into the queue. This procedure can be part of your application, called by a database-trigger, or provided via an external mechanism. In this section, we will provide an example of creating a stored procedure that puts a simple message in this queue.

Because our message is the parameter list itself, the procedure is fairly easy. We use the same code we used in earlier sections to create a parameter list. In addition to the variables we used, we define an `object` variable to hold the message we will put into the queue.

    …
      plist_object SRW_ParamList_Object;
    …

After creating the parameter list we create the actual message object using the object constructor.

    …
    plist_object := SRW_PARAMLIST_OBJECT(plist);
    …

Then we enqueue the message using the enqueue procedure provided by Advanced Queuing.

    …
    dbms_aq.enqueue(queue_name => 'myQueue',

    enqueue_options => enqueue_options,
    message_properties => message_properties,
    payload => PList_Object,
    msgid => message_handle);

    …

The message is put into the queue. Because we did not set up any message distribution, the message will stay in the queue until it is fetched by a dequeuing-procedure, which is discussed in the next section.

Note: For the exact syntax of `dbms_aq.enqueue` refer to the Advanced Queuing API Reference document. You'll find additional examples in the `srwAQsetup.sql` file included with your OracleAS Reports Services installation. Look for upcoming information about Reports APIs on the Oracle Technology Network, (`http://otn.oracle.com`)`.`

### 17.4.3 Creating the Dequeuing Procedure

A dequeuing procedure reads out all available messages in a queue and processes them. In our case, we want to read out the message and submit a job to the server using the parameter list that was attached to the message.

To accomplish this, we follow this example:

    BEGIN

    dequeue_options.wait := 1;

    loop

    DBMS_AQ.DEQUEUE(queue_name => 'myQueue',

    dequeue_options => dequeue_options,
    message_properties => message_properties,
    payload => PList_Object,
    msgid => message_handle);

    COMMIT;
    plist := plist_object.params;
    r_jid := SRW.RUN_REPORT(plist);

    end loop;
    exception when aq_timeout then

    begin

    NULL;

    end;

    END;

This code example will read out the queue until all messages have been processed. Time allowed for processing is determined by the time-out defined in the second line of code. This time-out defines the amount of seconds the dequeue procedure should wait for a message before creating a time-out exception.

The `DBMS_AQ.DEQUEUE``` built-in is provided by Advanced Queuing for reading out messages. It puts the payload of the message, the object that holds the information, into the object defined by the payload parameter.

Using `plist`, we extract the information from the payload object. As mentioned before, our object holds a parameter list. It is stored in the attribute `PARAMS` inside the object. The extracted parameter list is then handed over to `SRW.RUN_REPORT` for submitting the job.

If you want to avoid the need for invoking this dequeuing procedure by hand, you can run it as a job inside the database.