We are currently trying to create an automated scheduled csv export within the Oracle SQL Developer.

As I'm not to familiar with that topic at all, I would like you to ask for the best approach.

I know how to create a scheduled job within the Oracle SQL Developer but not how to write the following code for an iterative export, SQLCMD is not an option unfortunately.

Is there any other method, like creating a batch-file?

F.e.:

SQLCMD -S LocalHost -d database -E -Q "select * from table where convert(date,date-table)=convert(date, getdate())" -s "," -o "C:\Users*User\Desktop\VK_Export\VK_Stat.csv"*

CodePudding user response：

To create scripts for Sql Developer, you should use sqlcl , the Sql Developer Command Line Interface

SQLcl

If you are using windows, then you must create an script for doing so using cmd

sqlcl> set sqlformat delimiter ^ ' '
sqlcl> select name,code from table fetch first 2 rows only;
'Jane'^'1'
'Pete'^'2'

CodePudding user response：

I wouldn't use SQL Dev for scheduled tasks. SQL Dev is meant to be an interactive client. For scheduled tasks, use either the OS scheduler or the databases DBMS_SCHEDULER package, as appropriate. Since you want to produce a csv file, you could simply use a batch/shell script that calls sqlplus, ans schedule it with the OS scheduler or an appropriately configured DBMS_SCHEDULER.