| schedular in windows [message #154162] |
Fri, 06 January 2006 00:35  |
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  |
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 |
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 #154178 is a reply to message #154162] |
Fri, 06 January 2006 02:32 |
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 |
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
|
|
|
|
|
|
| Re: schedular in windows [message #154191 is a reply to message #154188] |
Fri, 06 January 2006 03:20 |
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 #154218 is a reply to message #154162] |
Fri, 06 January 2006 04:53 |
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 |
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?]
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: schedular in windows [message #154258 is a reply to message #154162] |
Fri, 06 January 2006 07:20 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
