Oracle® Fusion Middleware Publishing Reports to the Web with Oracle Reports Services 11g Release 1 (11.1.1) B32121-02 |
|
![]() Previous |
![]() Next |
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.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 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 scriptsrw_test.sql , included with your Oracle Reports Services installation. |