CREATE OR REPLACE PROCEDURE DATA_BKP
(in_sql   IN VARCHAR2,
dir_exp  IN VARCHAR2,
f_name   IN VARCHAR2
) is
 
  char_val         CHAR;
  num_val         NUMBER;
  var_val          VARCHAR2(4000);
  long_val         LONG;
  date_val         DATE;
  data_out        VARCHAR2(32767);
  ret_val           NUMBER;
  col_count       INTEGER;
  tabl_cur         NUMBER;
  cur_val          NUMBER;
  c_data          VARCHAR2(4000);
  rec_val         DBMS_SQL.DESC_TAB;
  long_data      INTEGER;
  fl_type           UTL_FILE.FILE_TYPE;
  file_val          BOOLEAN;
 
BEGIN
 
  tabl_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(tabl_cur, in_sql, DBMS_SQL.NATIVE);
  cur_val := DBMS_SQL.EXECUTE(tabl_cur);
  DBMS_SQL.DESCRIBE_COLUMNS(tabl_cur, col_count, rec_val);
 
  FOR ctr in 1..col_count
  LOOP
      CASE rec_val(ctr).col_type
           WHEN 1  THEN DBMS_SQL.DEFINE_COLUMN(tabl_cur,ctr,var_val,4000);
           WHEN 96 THEN DBMS_SQL.DEFINE_COLUMN(tabl_cur,ctr,char_val,1);
           WHEN 8  THEN DBMS_SQL.DEFINE_COLUMN_LONG(tabl_cur,ctr);
           WHEN 2  THEN DBMS_SQL.DEFINE_COLUMN(tabl_cur,ctr,num_val);
           WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(tabl_cur,ctr,date_val);
      ELSE
           DBMS_SQL.DEFINE_COLUMN(tabl_cur,ctr,var_val,2000);
      END CASE;
  END LOOP;

  fl_type := UTL_FILE.FOPEN(upper(dir_exp),f_name,'w',32767);
  FOR ctr in 1..col_count
  LOOP
            data_out := ltrim(data_out||chr(9)||lower(rec_val(ctr).col_name),chr(9));
  END LOOP;
  UTL_FILE.PUT_LINE(fl_type, data_out);
  IF NOT file_val THEN
            UTL_FILE.FCLOSE(fl_type);
  END IF;
 
   IF NOT file_val THEN
            fl_type := UTL_FILE.FOPEN(upper(dir_exp),c_data,'w',32767);
  END IF;
  LOOP
    ret_val := DBMS_SQL.FETCH_ROWS(tabl_cur);
    EXIT WHEN ret_val = 0;
    data_out := NULL;
    FOR ctr in 1..col_count
     LOOP
          CASE rec_val(ctr).col_type
                WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(tabl_cur,ctr,var_val);
                     data_out := ltrim(data_out||'"'||var_val||'"'||chr(9));
                WHEN 96 THEN DBMS_SQL.COLUMN_VALUE(tabl_cur,ctr,char_val);
                     data_out := ltrim(data_out||'"'||char_val||'"'||chr(9));
                WHEN 8 THEN DBMS_SQL.COLUMN_VALUE_LONG(tabl_cur,ctr,132768,0,long_val,long_data);
                     data_out := ltrim(data_out||'"'||long_val||'"'||chr(9));
                WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(tabl_cur,ctr,num_val);
                    data_out := ltrim(data_out||num_val||chr(9));
                WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(tabl_cur,ctr,date_val);
                    data_out := ltrim(data_out||to_char(date_val,'DD/MM/YYYY HH24:MI:SS')||chr(9));
         ELSE
                 DBMS_SQL.COLUMN_VALUE(tabl_cur,ctr,var_val);
                data_out := ltrim(data_out||'"'||var_val||'"'||chr(9));
         END CASE;
     END LOOP;
   UTL_FILE.PUT_LINE(fl_type, data_out);
  END LOOP;
  UTL_FILE.FCLOSE(fl_type);
  DBMS_SQL.CLOSE_CURSOR(tabl_cur);
END;
/

There is a directory called dir_csv (d:\backup_csv folder on the database server) created in the database.  Procedure is executed as:

exec data_bkp ('select * from table_name','dir_csv','table_name.CSV');