Schedule a query to run daily and save to excel

[Jebell96]

Hello!

I'm trying to figure out if I can schedule a query to run at a specific time daily, export it to an excel file, and then save it to a network drive. I had seen a forum from 2013 with an older version of PL/SQL developer that this wasn't possible, but wasn't sure if this is possible now in V15. The save to network drive would be a bonus, but not a deal break if it can only save to local C: drive. If it's possible, is it done through DBMS_SCHEDULER ?

Any insight would be greatly appreciated!

 

[Marco Kalter]

There is no option to export query results in PL/SQL Developer from the command-line. When using a scheduled job in the Oracle Database, it is executed on the database server so there is no simple option to export the results to a file system in Excel format.

If you want to run a scheduled job on the database server, you could consider to write the results to a separate result table so that it can easily be viewed or converted to Excel later through other tools.

 

[Jebell96]

So here's a couple of pictures related to our current process so you can see what we're doing. Based on these pictures, do you see any other way of simplifying/automating this process?

Of course after the 2nd image a save window shows up for us to select where to save the file (which we choose a network drive option). Rename, and save. But this process has to be done daily.

Any help would be greatly appreciated!

 

[Marco Kalter]

Instead of using the export button, you can can right-click on the result set and select "Copy to Excel" from the popup menu. Now you can use the following comment directives:

-- Records=All
-- Tab=<Name>
-- Excel=<Filename>.xlsx
select p.taxyr, ...

This forces the SQL Window to fetch all rows before the export, assigns a logical name to the tab, and therefore to the sheet in Excel, and defines the filename when using "Copy to Excel".

For the filename you can use environment variables by specifying %varname%, and specify %dbuser% and %dbname% for the database user and database name, as well as the following date/time variables:

%date%, %time%, %yyyy%, %mm%, %dd%, %hh%, %mi%, %ss%

For example:
-- Excel=C:\Excel\test_%date%.xlsx

You can include substitution variable values by specifying %&varname% in the filename. For example:
-- Excel=C:\Export\test_%&deptno%.xlsx

If the substitution variable named deptno has a value of 20, the filename will be test_20.xlsx.