Possible to automate/schedule sql developer query?

[Semidevil]

im trying to automate some of my tasks and one of the objective is to run a sql developer query and export the result. I'm using the spool function so as soon as I run it, it will automatically export, but is there a way for sql dev to automatically run the sql query on a weekly basis( in the background)?

 

[KB]

There are several ways to do this. Here are just two
1. You can use Task Scheduler to run a batch file that runs "sqlcmd"
https://msdn.microsoft.com/en-us/library/ms162773.aspx
https://www.sqlservercentral.com/Forums/Topic1693009-3077-1.aspx

2. Or Use the SQL Server Agent to schedule a SSIS job to do this
http://knowlton-group.com/using-ssis-to-export-data-to-excel/

 

[Semidevil]

Thanks. If I'm correct, I believe the two options are for sql server? I'm stuck with oracle sql developer.

I did manage to connect to sqlplus, which is the command line equivalent. However, my query is rather large with lots of variables and pivots, so it won't accept my variable which is more than 240 characters long.

I'm looking to see if the built-in dbms_scheduler can help, but so far I don't see an option for it to automatically run a query, only stored procedures(. I'm not familiar or know how stored procedures work)

 

[Mr.IncrediblyBored]

You can wrap your query in a procedure. Then use package utl_file in your proc to write the resultset to disk.

I'm not sure what you're passing to sqlplus but you can give it a sql file with your query. And pass variables as command line parms.

 

[Broheim]

..SSIS...

THE POWER OF CHRIST COMPELS YOU!!

*furiously douses the whole room in holy water*

Debugging SSIS is a nightmare. We've used it for integrations on some of our older projects and have had terrible experiences.

 

[HumblePie]

THE POWER OF CHRIST COMPELS YOU!!

*furiously douses the whole room in holy water*

Debugging SSIS is a nightmare. We've used it for integrations on some of our older projects and have had terrible experiences.

Old SSIS is a PITA, especially as most of it is never written by actual developers. Usually it's a DBA reading something another DBA did and copying it over as they can. SSIS code rarely follows code programming practices.

That being said, SSIS can be written and done very well especially if using current generation SSIS with visual studio.

Just don't go the other way and be a bad programmer writing SSIS. Sql still mostly uses "set" logic based structures and not standard programming structures for data flow. Seen more than a few software coders get screwed up on this aspect of SQL and either just throw it away or screw up with SQL.