Skip Headers
Oracle® Fusion Middleware Publishing Reports to the Web with Oracle Reports Services
11g Release 1 (11.1.1)

Part Number B32121-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

21.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 enables 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
DECLARE
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 key map file (for more information, see Section 18.13, "Using a Key Map 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
DECLARE
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 through 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
DECLARE
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 Oracle Reports Services installation.