Wednesday, October 1, 2008

SQL*Unloader to download data to a flat file

Is there a SQL*Unloader to download data to a flat file? (for DBA)
Oracle does not supply any data unload utilities. However, you can use SQL*Plus to select and format your data and then spool it to a file:
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off
Alternatively use the UTL_FILE PL/SQL package:
rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
utl_file.fclose(fp);
end;
/
You might also want to investigate third party tools like SQLWays from Ispirer Systems, TOAD from Quest, or ManageIT Fast Unloader from CA to help you unload data from Oracle.


No comments:

Post a Comment

Thanks to given comments.......

My Blog List