Schedule Report

[rb2005]

Hi,
Can I schedule report based on the presence of a row in Oracle report?
Is it possible if I schedule the report with event? If yes then how?
I am using CE10,CR10,Oracle 9i and business view
Thanks,
RB

 

[Turkbear]

Hi,
How would CE 'know' about the creation of the row?

You would probably have to design a Trigger in the Oracle database that, upon inserting a new row, would create some kind of file that CE's Event server could 'see'.

 

[rb2005]

Can you explain this? My report will run if one table load some data. So I want to create an event. I can create a insert trigger but do you have any clue how do I integrate the trigger with the CE event.

 

[mdwyer]

One way is to have the trigger write a file with the UTL_FILE package.

Another approach assumes the table is loaded by a process which writes a file when it completes successfully. For example, I have a unix shell script that runs from a cron. It launches sqlplus with a SQL load script. The load script ends with a 'spool' command, which names a specific output file.

That may be enough, but not if the spool file is created on the unix/db server when CE is running on a Windows server. So, the shell script does a conditional ftp:

if test -f LW_LOADED.lst #the spool file
then
        ftp -n $APS_IP_ADDRESS <<-EndScript
        user larftp xxx190
        put LCW_LOADED.lst
        bye
        EndScript
        rm LCW_LOADED.lst
fi

With the configuration of the windows server, the event file is D:\Inetpub\ftproot\LCW_LOADED.lst. When this file appears, the CE Event server kicks off the dependent reports. The event file does need to be removed* sometime before the new event file is created. I use a scheduled batch file on the windows server to accomplish this.

Be aware that the event server looks for the named file and triggers the event ONCE. After the event has been triggered, the event server will look for the file to NOT EXIST before it starts to look for it to exist.

*The file could also be renamed rather than being deleted.

 

[rb2005]

I am using Oracle 9i i.e. on AIX but CE on Windows. Can I use your code?

 

[mdwyer]

My code runs with Oracle9i on Solaris, CE on Windows2000. I would expect AIX to handle the 'if-file-exists' test, though it may need a slight tweak to the syntax. I'm also uncertain about the "here document" approach used to built the in-line ftp script (the stuff between <<-EndScript and EndScript, inclusive).