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

21.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 SRW_ParamList 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 SRW_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 Oracle Reports Services Security and Oracle 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 Event-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.

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

21.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 fourth—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.

21.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;

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

21.1.3 Including non-ASCII Characters in Parameter Names and Values

To use non-ASCII characters in user parameter names and values when using the Event-Driven Publishing API, you must include in your parameter list a parameter called DEFAULTCHARSET, with its value set to a valid character set name. This character set name can be specified with either the database's NLS_CHARACTERSET (for example, JA16SJIS) or IANA-defined character set name (for example, WINDOWS-31J). You must also ensure that the value of the DEFAULTCHARSET parameter matches the defaultcharset parameter specified in the rwservlet.properties file. Oracle Reports Services encodes non-ASCII user parameter names and values using the character set specified by DEFAULTCHARSET, allowing you to use the Event-Driven Publishing API for reports with non-ASCII characters in parameter names and values.

Note:

If you do not add a parameter called DEFAULTCHARSET to your parameter list, Oracle Reports Services encodes your user parameter names and values using the database's NLS_CHARACTERSET.

21.1.4 Submitting 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 Oracle Reports Servlet (rwservlet). 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 Oracle Reports Servlet (rwservlet) you will use to process the request.
SERVER identifies the Reports Server to be used in conjunction with Oracle Reports Servlet (rwservlet).
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 (through 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.

21.1.5 Checking 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 executing the rwservlet Web command showjobs.

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.

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