yes yes, most of the client tools allows to dump directly into xls, html, xml file which take a transfer bandwith to transfer data from server to client. so for big data this system might not be so worth it.you can find lots of way to dump text into file. here is one of the way i have been doing since 2 yrs.
create or replace procedure dump_table_to_csv(l_query in varchar2,
p_dir in varchar2,
p_filename in varchar2,
v_separator VARCHAR2 DEFAULT ‘|’,
DateFORMAT VARCHAR2 DEFAULT ‘DD-MON-YYYY’ )
AS
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
BEGIN
l_output := utl_file.fopen( p_dir, p_filename, ‘w’,’10000′ );
EXECUTE IMMEDIATE ‘ALTER SESSION SET NLS_DATE_FORMAT=”DD-MON-YYYY”’;
dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
utl_file.put( l_output, l_separator || l_descTbl(i).col_name );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := v_separator;
end loop;
utl_file.new_line( l_output );
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := ”;
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := v_separator;
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );
EXCEPTION
WHEN OTHERS THEN
raise;
END;
/
and where first argument is your query which generates the data.
2nd one is name of the directory alias
3rd filename you want to make it.
4th seperator, by which you want to separate data from each other. default is ‘|’
and 5th is Date format, in different country way timeformat is different.
e.g.
exec dump_table_to_csv (‘select * from tabs’, ‘DATA_PUMP_DIR’,’test.txt’,’,’,’YYYY-MM-DD’);
