PL/SQL report file automatically run and export result?

Question

I am using PL/SQL developer.

I have a report file (.rep) that fetches some data based on time period, for ex. from 1st July 2012 to 2th July 2012. I use this report by running it, entering &date_from and &date_to, and then export it in HTML via corresponding button.

I want to automate this task because basically this report needs to be executed on daily basis with today's date as a parameter. I have modified a code a bit so that now it uses trunc(current_date, 'DDD') as a parameter so that I do not have to enter it manually. Next I need to:

1. run this report as a scheduled task without my interaction.
2. get an HTML export copied in a folder with the date that was used as a parameter inside report (so, basically current_date).

The pseudocode of my wish script would be like the following:

reportDate = get_current_date_without_time
run my_report.rep
mkdir c:\somefolder\reportDate 
copy my_report_result_as_HTML c:\somefolder\reportDate

Is it possible? If so, then how?

Answer 1

PLSQL Developer is an IDE for building Oracle stored procedures, from third party Allround Automations. Its functionality includes a feature for building reports. However, these reports are mainly for the use of developers not civilians. So the reports can only be run interactively, through the GUI.

Allround Automations do also offer Query Reporter, a free tool which can run .REP files written in PLSQL Developer. Find out more.

This tool has a command line option for running reports. So you could write a .BAT file which does all the other stuff you want (getting the time, creating the directory), then use the QueryReport command to run the report and save it as html. This example is from the page to which I linked above.

QueryReporter userid=scott/tiger@chicago exec=DeptEmp.rep html=DeptEmp.html quit

You would then create a job in the Windows scheduler to run the batch file each day.