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

[fpayande]

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

[badrinathn]

Multiple ways:

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

Badrinath

[gbrabham]

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

[gbrabham]

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

[castlerock]

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

[slimdave]

you can't spool from PL/SQL -- that's a SQL*Plus command. UTL_FILE is the way to go.

[castlerock]

"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