Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to Schedule Query Execution ?
Pankaj_Jha wrote:
> Brian Peasland wrote:
>
>> Pankaj_Jha wrote: >>> Dear All, >>> >>> I have written a SQL Query for generating report. I want this query to >>> get executed automatically every day at 9:00 AM. Suppose the query is >>> like this >>> >>> Select e.empname, e.dept, s.basicsalary, s.hra, s.da, s.grosssalary >>> from employee e, salary s >>> where e.empid=s.empid >>> >>> Can U tell me how to do this. >>> >>> Thanks in advance >>> >>> Pankaj Jha >>> >> Which version of Oracle? >> >> What do you want done with the output from the above? >> >> Cheers, >> Brian >> >> >> -- >> =================================================================== >> >> Brian Peasland >> dba_at_nospam.peasland.net >> http://www.peasland.net >> >> Remove the "nospam." from the email address to email me. >> >> >> "I can give it to you cheap, quick, and good. >> Now pick two out of the three" - Unknown
Put the following in a text file named my_script.sql:
spool c:\directory\my_output.txt
Select e.empname, e.dept, s.basicsalary, s.hra, s.da, s.grosssalary
from employee e, salary s
where e.empid=s.empid;
spool off
exit
Then create a batch file called my_sql.bat which contains:
set ORACLE_HOME=c:\oracle\10.2.0
sqlplus system/manager @my_output.txt
Schedule the batch file with your Windows scheduler.
You may have to make some substitutions to the above as I do not know your directory structure. The above also assumes that you are running on Windows. If using Unix/Linux, slight modifications will be needed.
An alternative, since you are using 10g, is to schedule the above with
DBMS_SCHEDULER.
HTH,
Brian
-- =================================================================== Brian Peasland dba_at_nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - UnknownReceived on Thu Aug 31 2006 - 10:08:35 CDT