-
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?
-
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.
-
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
-
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
-
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
-
you can't spool from PL/SQL -- that's a SQL*Plus command. UTL_FILE is the way to go.
-
"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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|