DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How to use Spool from a PL/SQL Stored Procedure?

  1. #1
    Join Date
    Mar 2002
    Posts
    38

    How to use Spool from a PL/SQL Stored Procedure?

    Hi Friends,

    I had a requirement where I have to use spool technique in a stored procedure.From thwe stored procedure the output data has to be spooled to a file. How I can do this ?

    The following is my code.Pls modify the code and give me the script. As I am newbie let me know if any errors are there.

    I need to call this procedure from a Scheduler on a fixed date.How can I do this.

    CREATE OR REPLACE PROCEDURE GETDATE
    AS
    CURSOR C1 IS SELECT * FROM NEWAFFAIRS
    WHERE DOB BETWEEN TO_DATE('07-'||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'MON')||'-'
    ||TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYY'),'DD-MON-YYYY') AND TO_DATE('07-'||TO_CHAR((SYSDATE),'MON')||'-'
    ||TO_CHAR((SYSDATE),'YYYY'),'DD-MON-YYYY');
    CREC C1%ROWTYPE;
    BEGIN
    OPEN C1;
    LOOP
    FETCH C1 INTO CREC;
    EXIT WHEN C1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CREC.NEWID||','||CREC.SURNAME||','||CREC.NAMES||','||CREC.DOB);
    END LOOP;
    CLOSE C1;
    END;


    Thanks In Advance.

    Kishan
    SUROOP B

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Use UTL_FILE package to write to a file, and use DBMS_JOB package to schedule it.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2004
    Posts
    1
    Check the following example for UTL_FILE usage.

    http://www.jlcomp.demon.co.uk/faq/utl_file.html

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