Home » Infrastructure » Windows » schedular in windows
schedular in windows [message #154162] Fri, 06 January 2006 00:35 Go to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
hi,
please can anyone tell the procedure to generate automatic spool of a query on oracle 9i,using windows schedular
Re: schedular in windows [message #154164 is a reply to message #154162] Fri, 06 January 2006 01:10 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

1.Write desired SQL script.

For eg:
 
 rem #####################
 rem desired sql script
 rem filename demo_scr.sql
 rem #####################

 spool c:\demo.out
 select ename,sal from emp;
 spool off
 exit
 


2. Write a batch file
for eg.
-- batchfile.bat (note the extension shoule be .bat)
 
 sqlplus username/password @demo_scr.sql
 



3. Schedule the execution of this batchfile from windows scheduler.

Re: schedular in windows [message #154167 is a reply to message #154162] Fri, 06 January 2006 01:24 Go to previous messageGo to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
its still not working !!!!
i created a notepad file scr_sql_script2.sql

CONNECT username/password
SPOOL C:\job.lst
SET LINESIZE 100
SET PAGESIZE 50
select job,last_date,next_date,broken,substr(what,20,150) from dba_jobs
order by job;
SPOOL OFF
EXIT;

then a notepad file called get_job.bat

sqlplus username/password @C:\sc_sql_script2.sql



plz help


after this how do i execute it,pardon me in it seems silly question


Re: schedular in windows [message #154174 is a reply to message #154167] Fri, 06 January 2006 02:03 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


Just run that file on command prompt

 c:\> get_job.bat
and look into spool file, your work is done.
Re: schedular in windows [message #154178 is a reply to message #154162] Fri, 06 January 2006 02:32 Go to previous messageGo to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
C:\>get_job.bat
'get_job.bat' is not recognized as an internal or external command,
operable program or batch file.


is the error i am getting


kindly help me resolve this.


Re: schedular in windows [message #154181 is a reply to message #154178] Fri, 06 January 2006 03:00 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
It works perfectly for me.

are you running it from the same location where it was created? if not then run from the location where it is stored.

For eg. if the file is stored at c:/scripts then first of cd to that location

 c:\>cd scripts
        c:\scripts> get_job.bat 
 
icon9.gif  Re: schedular in windows [message #154188 is a reply to message #154162] Fri, 06 January 2006 03:10 Go to previous messageGo to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
i am doing it perfectly,my file is in c drive and i am running it ,but still no progress

dony know whats wrong with my pc
Re: schedular in windows [message #154191 is a reply to message #154188] Fri, 06 January 2006 03:20 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

C:\>copy con file1.sql
spool outfile.log
select * from tab;
spool off
exit
^Z
        1 file(s) copied.

C:\>copy con get_job.bat
sqlplus system/manager @file1.sql
^Z
        1 file(s) copied.

C:\>get_job.bat

C:\>sqlplus system/manager @file1.sql

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 6 14:47:50 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SYSCATALOG                     SYNONYM
CATALOG                        SYNONYM
TAB                            SYNONYM
COL                            SYNONYM
C:\>dir out*
 Volume in drive C has no label.
 Volume Serial Number is 4CC3-658A

 Directory of C:\

01/06/2006  02:47 PM            15,470 outfile.log
               1 File(s)         15,470 bytes
               0 Dir(s)   9,441,751,040 bytes free


look above it worked perfectly for me.

Can you copy and paste the screen shots like i did above?
Re: schedular in windows [message #154198 is a reply to message #154162] Fri, 06 January 2006 03:55 Go to previous messageGo to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
thanks tarun
finally its working now if i want to schedule thrice a day ,how do i do it,and i want result in different files each day.
Re: schedular in windows [message #154203 is a reply to message #154198] Fri, 06 January 2006 04:07 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member


Pooja,

you can use windows' at command.

check the help on this command
 c:\>at/? 


and google it more to know its proper usage.

Re: schedular in windows [message #154212 is a reply to message #154203] Fri, 06 January 2006 04:43 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

You might even check this ..
http://www.iopus.com/guides/winscheduler.htm

-Sai Jeedigunta
sai.jeedigunta@gmail.com
Re: schedular in windows [message #154218 is a reply to message #154162] Fri, 06 January 2006 04:53 Go to previous messageGo to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
thanks tarun
your prompt replies were very helpful
i have scheduled in the schedular,but my problem is that whenever my batch file runs,it overwrites the previous result.I want to configure it such that automatically when it runs ,a new file is created to write the result(file name such that its a snap of current time and date)
Re: schedular in windows [message #154220 is a reply to message #154218] Fri, 06 January 2006 05:10 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Pooja,

Well, i dont think windows is that interactive . This would have been an easy task if you were running it on any *UNIX OS .

But i am afraid you have to pass the name of the spool file everytime. And if you get any solution for dynamic spooling then do let us know.

[BTW: Just saw your location , are you working in bangalore?]



icon9.gif  Re: schedular in windows [message #154238 is a reply to message #154162] Fri, 06 January 2006 06:05 Go to previous messageGo to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
oh is it?
Then my problem cannot be solved?I want results in different files ,so that i can verify result later.
If its not possible then there is no point of schedular.
or i can have multiple jobs in schedular(same query for different times),the the output will be in different files.......
This seems to be the only solution
Re: schedular in windows [message #154243 is a reply to message #154162] Fri, 06 January 2006 06:09 Go to previous messageGo to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
http://forums.oracle.com/forums/thread.jspa?threadID=348526&tstart=0


can anyone tell me what the discussion all about?I am using oracle 9i
icon14.gif  Re: schedular in windows [message #154248 is a reply to message #154243] Fri, 06 January 2006 06:20 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

You can try this link i believe
http://georgenet.net/oracle/

search for " How can I date/time stamp the spool filename ?" in the page ..

good luck
-Sai Jeedigunta

------------------
sai.jeedigunta@gmail.com

Re: schedular in windows [message #154250 is a reply to message #154248] Fri, 06 January 2006 06:30 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

@sai.jeedigunta
Bingo , that worked pretty fine.

@pooja
Perfect solution for the problem. Razz

[Updated on: Fri, 06 January 2006 06:31]

Report message to a moderator

Re: schedular in windows [message #154255 is a reply to message #154162] Fri, 06 January 2006 07:01 Go to previous messageGo to next message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
i wanted the date to be filename change to be automatic,but the user has to enter them Razz
Re: schedular in windows [message #154258 is a reply to message #154162] Fri, 06 January 2006 07:20 Go to previous message
pooja_g
Messages: 56
Registered: December 2005
Location: India
Member
sorry
the script is fine with few modification ...
by the end of the the proper solution for dynamic spooling is.....

The sql file

alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
set linesize 150
column dt new_value _dt
column txt new_value _txt
select to_char(sysdate,'yy_mm_dd_hh24_mi') dt from dual;
select to_char('.txt') txt from dual;
spool /tmp/job_&_dt&_txt
select job,last_date,next_date,broken,substr(what,20,50),total_time from dba_jobs where job=7027 or job=7069;
SPOOL OFF
exit;



Now run the batch file ......

U finally get the result
Previous Topic: Ora-00000
Next Topic: slow oracle connection on windows
Goto Forum:
  


Current Time: Thu Mar 28 11:29:22 CDT 2024