Using DBMS_JOBS
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Using DBMS_JOBS

  1. #1
    Join Date
    Feb 2001
    Posts
    23
    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

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width