Using DBMS_JOBS

[vg]

I have a SQL script, which spools the output to a txt file.
Can I use DBMS_JOBS utility so as to set this script run in specified intervals..?

What I heard from someone is that, only Stored Procedures can be set as jobs in DBMS_JOBS.

If so, I would like to know how to spool the output of a sql query to a txt file using a PL/SQL script.

Thanks

VG

[ales]

Originally posted by vg
I have a SQL script, which spools the output to a txt file.
Can I use DBMS_JOBS utility so as to set this script run in specified intervals..?

No.

What I heard from someone is that, only Stored Procedures can be set as jobs in DBMS_JOBS.

Yes. Stored Procedures and Anonymous PL/SQL Blocks.

If so, I would like to know how to spool the output of a sql query to a txt file using a PL/SQL script.

You have to choose between two ways:
A. Leave the script as is and use OS scheduling to run it in specified time - at on Win, crontab on unix and similar OS
B. Convert the SQL*Plus script to stored procedure and
- use the utl_file package for spooling the output to OS file
OR
- insert the output to a table with a CLOB/LONG/VARCHAR2 column (a VARCHAR2 column if 4000 chars are enough, LONG if you can't use CLOB). It's possible to read all these datatypes with SQL*Plus.

Regards,

Ales