Home » SQL & PL/SQL » SQL & PL/SQL » Automatic Spool File Generation (Oracle 9i)
Automatic Spool File Generation [message #375929] Mon, 15 December 2008 06:40 Go to next message
pavanankb
Messages: 2
Registered: December 2008
Junior Member
I have an oracle 9i database. I need to generate a spool file (text or csv), which will be the result of an SQL Query. But this spool file generation code has to trigger or execute at midnight. When this code is executed, it should create this Spool file and dump it into a particular folder. There shouldn't be any manual intervention in this process, except for the first-time.
Re: Automatic Spool File Generation [message #375931 is a reply to message #375929] Mon, 15 December 2008 06:45 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Create a script and schedule it to execute at the appropriate times. You can, for example, use "cron" on Unix/Linux, "at" on Windows, or if you are on 10g you can even use Oracle's DBMS_SCHEDULER.
Re: Automatic Spool File Generation [message #375932 is a reply to message #375931] Mon, 15 December 2008 06:52 Go to previous messageGo to next message
pavanankb
Messages: 2
Registered: December 2008
Junior Member
Sorry that I did not specify my platform on which oracle 9i database is working. It is a windows 2003 server PC. Can you please tell me what kind of a script should I write. Can I write a PL/SQL block or Trgger or Procedure/Function. How do I schedule it in Oracle 9i? Thank you so much.
Re: Automatic Spool File Generation [message #375940 is a reply to message #375929] Mon, 15 December 2008 07:16 Go to previous messageGo to next message
subhra_88
Messages: 14
Registered: April 2007
Location: Bangalore
Junior Member
In oracle 9i you can use the package dbms_job to schedule jobs.
Re: Automatic Spool File Generation [message #376058 is a reply to message #375932] Mon, 15 December 2008 19:05 Go to previous message
Barbara Boehmer
Messages: 8621
Registered: November 2002
Location: California, USA
Senior Member
Substitute your own directory paths, file names, queries, username, and password in the following example.

Create a .sql file from SQL*Plus with your spool commands and query:

-- c:\oracle11g\test.sql:
spool c:\oracle11g\test.txt
select * from emp;
spool off
exit

Create a .bat file from your operating system to run your .sql file:

-- c:\oracle11g\test.bat:
C:\app\Barbara\product\11.1.0\db_1\BIN\sqlplus scott/tiger @c:\oracle11g\test.sql

Use the windows task scheduler to schedule running your .bat file:

http://www.iopus.com/guides/winscheduler.htm

Although you can use dbms_job in 9i to schedule running pl/sql procedures and such, you cannot use it to schedule running a sql script with a spool command. That is why you need to schedule it using the task scheduler from the operating system. Alternatively, you could write some pl/sql code to output the results to a file using utl_file, then you could use dbms_job to schedule it. I believe the dbms_scheduler, which has more options, was not available until later versions.
Previous Topic: delete column data every 30 days
Next Topic: Load Utility in DB2 - Alternative in Oracle?
Goto Forum:
  


Current Time: Fri Dec 02 23:05:45 CST 2016

Total time taken to generate the page: 0.12921 seconds