Spool with Oracle

Step1 Open Notepad and write the following lines. Then save it as myscript.sql in c:

spool c:\tajfile1

select * from emp;

spool off;

exit;

Step2.Open another notepad and write the following lines and save it as tajbatch.cmd file in thec:\

sqlplus scott/tiger@orclnew @c:\myscript.sql

Step3.Now you can double click on the tajbach.cmd in c:\ and you will find the output inc:\tajfile1.lst file.Step4.How to schedule this script.

Go to Control Panel ->Scheduled Tasks and make a new Schedule to call the tajbatch.cmd file.This will run this file and execute the script at the specified interval.

Step 5.How to Obtain output in Excel

set term off 

set echo off 

set pages 0

head on

emb on

newp noneset

markup html on

spool on

spool c:\emp.xls

select * from emp;

spool off

set markup html off

spool off

exit;

Step6.How to dynamically Generate a file NameWrite the following lines of code in script file say,.. myscript2.sql, then make a call thisscript from the .cmd file as shown in Step 2

set termout off;

set echo off;

set pagesize 9999;

col sys_date new_value curr_date;

select to_char(sysdate,'yyyy_ddmon_hh24_miss')||'.log' sys_date 

from dual;

spool ArevaSystemSnag_&curr_date;

set termout on;

select * from emp;

spool off;

exit;

Step7.Finally, combining all these options, we will now write a complete script to generate data inan Excel file with the file name of the output file having the timestamp in the name itself.Please note that the same has been used for generating the data on the live Areva Systemin a Snap file.

set termout off;

set echo off;

set pagesize 9999;

col sys_date new_value curr_date;

select to_char(sysdate,'yyyy_ddmon_hh24_mi_ss')||'.xls' sys_date

from dual;

set pages 0

head on

emb on

newp noneset markup html on

spool on

spool ArevaSystemSnag_&curr_date;set termout on;

select * from emp;

spool off;

exit;