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

Thread: How can I send the output to a disk file from a pl/sql program?

  1. #1
    Join Date
    Oct 2001
    Posts
    9

    Unhappy How can I send the output to a disk file from a pl/sql program?

    How can I send the output to a disk file from a pl/sql program?

  2. #2
    Join Date
    Jan 2001
    Posts
    642
    Multiple ways:

    1) use spool (if connected to the host)
    2) use the utl package.

    Badrinath
    There is always a better way to do the things.

  3. #3
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    SPOOL IS A GOOD WAY. What type of format do you want it in (ie,
    comma delimited ??) ???

    You can spool to an output file then concatenate your result set
    with whatever delimiter you want, or keep it in report format

    Gregg

  4. #4
    Join Date
    Apr 2003
    Location
    South Carolina
    Posts
    148
    Here's an example of spooling to a file (name of file has date string in it), then writting out a comma delimited file...

    Good Luck,
    Gregg


    set space 0
    set pagesize 0
    set feedback off
    set verify off
    set termout off
    set heading off
    set serveroutput off
    set echo off
    set show off
    set trim off
    set concat on
    set linesize 60

    column DAY_NAME new_value file_day noprint

    select trunc(SYSDATE) DAY_NAME FROM DUAL;

    host del c:\1\ray_file*.txt
    spool c:\1\Ray_File_&&whichday..txt

    select
    to_char(REPORTING_DATE,'YYYYMMDD')||','||LOCATION_IDENTIFIER||','||PART_ID||','||UNIT_OF_MEASURE||', '||QTY_ON_HAND||','
    from parts
    where reporting_date = upper('&WHICHDAY') and qty_on_hand > 0
    order by location_identifier, catalog_id;

    spool off

  5. #5
    Join Date
    Jan 2003
    Posts
    141
    You can either use spool or UTLFILE to write into the disk

    IN init.ora file, set the parameter as
    UTL_FILE_DIR='E:\utlfile_dir'

    Create a folder utlfile_dir in the E Drive and share the folder


    Then in your pl/sql block user
    lv_utldir=''E:\utlfile_dir';
    lv_filename='outputfilename.txt' ;
    lv_filehandler:=UTL_FILE.FILE_TYPE;


    lv_filehandler :=UTL_FILE.FOPEN(lv_utldir,lv_filename,'W');

    UTL_FILE.PUT_LINE(' .............')

    UTL_FILE.FFLUSH(lv_filehandler);

    UTL_FILE.FCLOSE(lv_filehandler);


    You can save the file in .csv extension,.doc etc.

    Hope this one helps you

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    you can't spool from PL/SQL -- that's a SQL*Plus command. UTL_FILE is the way to go.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  7. #7
    Join Date
    Jan 2003
    Posts
    141
    "How can I send the output to a disk file from a pl/sql program?"
    What i meant is...
    Using pl/sql program writing the o/p to a file

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