3

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
kaytrance
  • 2,657
  • 4
  • 30
  • 49
  • Is this an Oracle Reports program (the .rep extension suggests it is)? If so, which version of the product are you using? – APC Jul 25 '12 at 11:00
  • yes, I believe it is, PLSQL Developer 9.0.3.1641 – kaytrance Jul 25 '12 at 11:18
  • Oracle Reports is a proprietary tool for developing reports, including complex layouts and formatting. It has *nothing* to do with PLSQL Developer which is a third party IDE to build stored procedures. You need to get your facts straight if you want us to help you. – APC Jul 25 '12 at 11:21
  • well then, as I pointed in the first line of my initial post, I use PLSQL Developer, and it's version 9.0.3.1641. – kaytrance Jul 25 '12 at 11:28
  • Sorry, I didn't realise that PLSQL Dev also stored its report programs in fiels with a .REP extension. – APC Jul 25 '12 at 12:03

1 Answers1

4

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.

APC
  • 144,005
  • 19
  • 170
  • 281
  • No batch file is needed, you can schedule the task to call QueryReporter and there is a separate field for parameters (at least in Windows 7) – MRR0GERS Nov 15 '13 at 01:09