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
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.14, "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
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
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.