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’);