Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: How to Schedule Query Execution ?

Re: How to Schedule Query Execution ?

From: Brian Peasland <dba_at_nospam.peasland.net>
Date: Thu, 31 Aug 2006 15:08:35 GMT
Message-ID: <J4vBEI.1ut@igsrsparc2.er.usgs.gov>


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

>
> =============================================================
>
> Dear Brian
>
> I am Using Oracle 10g client on my machine to connect with Oracle 10g
> Server to connect with Server (Since Database is actually on Server).
>
> Also i want the Out put of the query to be directed to a .xls or .txt
> file.
>
> Thanks
>
> Pankaj Jha
>

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" - Unknown
Received on Thu Aug 31 2006 - 10:08:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US