Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

Oracle Job Scheduling Execution

Without being too technical about, I would like to know the usual steps that are taken to create and schedule a Job in Oracle and then have the server execute the job(Ex. A query that pulls the overnight orders) so that the Server (Windows)  at a specified time and perhaps save a csv copy of this file in a predefined location.
0
Benki Canoso
Asked:
Benki Canoso
1 Solution
 
slightwv (䄆 Netminder) Commented:
You could use dbms_scheduler and a stored procedure to create the CSV and write it to a directory on the database server using UTL_FILE.  This requires writing some code but does keep it all in the database.

There are MANY samples of UTL_FILE and DBMS_SCHEDULER in the docs and online.

Here is the first link I found with a search:
http://nimishgarg.blogspot.com/2011/09/create-csv-file-using-plsql.html

You could also go with the simple:
Create a sql script and use sqlplus and schedule it from Windows Task Scheduler.

If the resulting CSV will not exceed 4000 characters something like this.

Create a script named mycsv.sql
set lines 4000
set trimspool on
set pages 0
set feedback off
spool c:\folder\mycsv.csv
select col1 || ',' || col2 || ',' || col3 from some_table;
spool off

Then from a CMD prompt:

sqlplus user/password @c:\script_dir\mycsv.sql
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now