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@xxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxxx
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
.

Relevant Pages

  • Re: MailMerge hangs and crashes with Access on Server
    ... Since I am fairly new to working in this environment, I am not sure what you mean by an "Access group" with help to restructuring the query? ... "Peter Jamieson" wrote: ... All I know is that the performance of queries depends to a large extent on whether, for example, a join is performed on the client side, or on the server side, incurring no network traffic and potentially benefitting from caching on the server. ... There are no dialog boxes, and actually, I have now split the database, and have kept the Word templates and the Access frontend locally on my machine, and moved the backend to the server. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Performance Benchmarks?
    ... Are attribute relationships defined properly on the dimensions? ... If you run Profiler against the server, how long is spent in "Query ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • High CPU in client (Excel, OWC, Proclarity, etc.) accessing Analysis Services
    ... the OWC10 with 3 dimensions on the row axis, ... The largest size of any of these 4 dimensions < 360 members. ... Performance Guide to optimize the query, the cube, the server, etc. ...
    (microsoft.public.sqlserver.olap)
  • Re: Performance Benchmarks?
    ... adding memory on the server can help you; more data can be cached on the ... Here is my test query. ... )} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ... I have created aggregations (Partitions tab in cube design). ...
    (microsoft.public.sqlserver.olap)
  • Re: MailMerge hangs and crashes with Access on Server
    ... restructuring a complex query. ... discards 99% of them), or on the server side, incurring no network ... the database is locked and Word will not open the data source. ... have kept the Word templates and the Access frontend locally on my machine, ...
    (microsoft.public.word.mailmerge.fields)