×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

sql submitted on timer

sql submitted on timer

sql submitted on timer

(OP)
I need to run a SQL script unattended on a time/date basis.
For example, I need a select script to run every hour to extract a set of records into a delimited text file. What is the best way to accomplish this? Im working with Oracle Pl/Sql but have VB available if necessary. However, I want to use the most efficient method possible because the script will be running against a fairly large database.

Any advice, assistance in creating a text file with sql and running it unattended would be appreciated

bob

RE: sql submitted on timer

If your database is on a UNIX system you can use cron to
schedule a sql script to start your PL/SQL.

RE: sql submitted on timer

(OP)
It is on NT. There is an "at" command in nt. which i'm sure is similar. Theoretically I could schedule it to run sql/plus on a periodic basis but ive never had any luck getting it to work correctly.

thanks for the reply


RE: sql submitted on timer

You could use the dbms_job package inside Oracle
to schedule the running of a stored procedure. The
stored procedure in turn could perform the select
into a cursor object, open up a file for writing,
then loop through the cursor and write out to your
file.

RE: sql submitted on timer

(OP)
I found some references to dbms_job, but even in my best ref books there is no description of how to use it. It is mentioned by name but there are NO examples of how to use it. Is there any chance you could point me to any examples of using dbms_job?

IT would be a great help if I could find some details on it.

thanks for the reply

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close