Home » SQL & PL/SQL » SQL & PL/SQL » Scheduling the select statement
Scheduling the select statement [message #184845] Fri, 28 July 2006 05:34 Go to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
Hi I need to run a select statement periodically every week and the output spool file is to be sent by mail. Can any one tell me how to schedule to run the select statement using pl/sql?

Thanks
Hari
Re: Scheduling the select statement [message #184848 is a reply to message #184845] Fri, 28 July 2006 05:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to run a SQL*Plus script, then you'll be looking at using CRON (Unix, Linux) or Windows Scheduler (Go on, guess the OS)

If you want to run a pl/sql procedure, (say using UTL_FILE to output the data, then you can use DBMS JOB or Dbms_Scheduler if you're using 10g
icon5.gif  Re: Scheduling the select statement [message #184911 is a reply to message #184848] Fri, 28 July 2006 10:47 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
I need to use procedure to run a select statement and the lst file to be mailed as attachment to number of users.
Can you help
Thanks
Re: Scheduling the select statement [message #184931 is a reply to message #184911] Fri, 28 July 2006 15:12 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Now that you have taken the advice from JRowbottom on how to run a SQL script at a certain time, you can look at the unix mailx command to email the output to multiple users.

Re: Scheduling the select statement [message #184963 is a reply to message #184845] Sat, 29 July 2006 00:51 Go to previous messageGo to next message
hari_bk
Messages: 110
Registered: March 2006
Senior Member
I can not use Unix in this case! I need to do this task using only pl/sql procedure! can any one send me example to generate spool file periodically and mail the attachment to number of users!

Thanks
Hari
Does anyone has a Stored Procedure Sample for Scheduling ?? [message #184981 is a reply to message #184963] Sat, 29 July 2006 09:10 Go to previous messageGo to next message
vx_2004
Messages: 10
Registered: July 2006
Junior Member
Does anyone have a sample Stored Procedure which schedules a
SQL Script written in Notepad ??

Please share it here. Unix Admin does not like people to schedule CRON Jobs ??

Thanks
Re: Scheduling the select statement [message #184995 is a reply to message #184845] Sat, 29 July 2006 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>mail the attachment to number of users!
I hope you are on 10g & can use UTL_SMTP
http://asktom.oracle.com has many fine coding examples.
Re: Scheduling the select statement [message #184996 is a reply to message #184845] Sat, 29 July 2006 11:45 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>Does anyone have a sample Stored Procedure which schedules a SQL Script written in Notepad ??
I wonder if you have ever written a stored procedure.
For scheduling use DBMS_JOB.
For coding examples, visit http://asktom.oracle.com
Re: Scheduling the select statement [message #184997 is a reply to message #184996] Sat, 29 July 2006 12:31 Go to previous messageGo to next message
vx_2004
Messages: 10
Registered: July 2006
Junior Member
Laughing Yes, I have written SP.

These are mainly DDL Scripts, usually they will be run one time....

Re: Scheduling the select statement [message #185000 is a reply to message #184845] Sat, 29 July 2006 13:06 Go to previous messageGo to next message
BlackSwan
Messages: 25049
Registered: January 2009
Location: SoCal
Senior Member
>These are mainly DDL Scripts, usually they will be run one time....
DDL from within SP???????????
Why not use straight SQL & avoid the overhead of the context switch between the SQL engine & the PL/SQL engine?
Re: Scheduling the select statement [message #185006 is a reply to message #185000] Sat, 29 July 2006 17:56 Go to previous message
vx_2004
Messages: 10
Registered: July 2006
Junior Member
Well the idea is it can be run by anyone later on, or just give the script to a UNIX Admin and he should be able to run.

I would be finishing my contract, so that's was just an option
being explored by others that have a SP or some way by which
it can be scheduled
by UNIX admins / other support staff.

Previous Topic: How to Modify this Query to make it loop through Certain defined intervals
Next Topic: updating a view
Goto Forum:
  


Current Time: Fri Dec 09 09:54:21 CST 2016

Total time taken to generate the page: 0.12274 seconds