Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle Job Scheduling Execution

Posted on 2014-11-27
1
Medium Priority
?
380 Views
Last Modified: 2014-11-30
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
Comment
Question by:Benki Canoso
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40469811
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question