Printable View
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
No.Quote:
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..?
Yes. Stored Procedures and Anonymous PL/SQL Blocks.Quote:
What I heard from someone is that, only Stored Procedures can be set as jobs in DBMS_JOBS.
You have to choose between two ways:Quote:
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.
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