CREATE TABLE CITIES
(
  ID    NUMBER(5),
  NAME  VARCHAR2(30 CHAR)
)
;


SET DEFINE OFF;
Insert into CITIES
   (ID, NAME)
 Values
   (1, 'AMSTERDAM');
Insert into CITIES
   (ID, NAME)
 Values
   (2, 'UTRECHT');
Insert into CITIES
   (ID, NAME)
 Values
   (3, 'THE HAGUE');
Insert into CITIES
   (ID, NAME)
 Values
   (4, 'ROTTERDAM');
COMMIT;

CREATE OR REPLACE function CSVDataTest (InputData dbms_tf.table_t,
                                        Delimiter varchar2 default ',',
                                        Quote     varchar2 default '"',
                                        Escape    varchar2 default '\') return clob sql_macro as
     
  DelimiterRecord varchar2(24) default '||Delimiter||';
  ColumnName      varchar2(200);
  ColumnRecord    varchar2(300);
  HeaderRecord    clob;
  DataRecord      clob;
  SQLStatement    clob;
  
  
  begin
    for i in 1..InputData.column.count 
      loop
        ColumnName := InputData.column(i).description.name;
        
        case
          when InputData.column(i).description.type in (dbms_tf.type_varchar2, 
                                                        dbms_tf.type_char, 
                                                        dbms_tf.type_clob)
            then
              if Quote is not null
                then
                  ColumnRecord := 'replace('||ColumnName||','''||Quote||''','''||Escape||Quote||''')';
                  ColumnRecord := ''''||Quote||'''||'||ColumnRecord||'||'''||Quote||'''';
              
                else
                  ColumnRecord := ColumnName;
              end if;
              
          when InputData.column(i).description.type = dbms_tf.type_number
            then
              ColumnRecord := 'to_char('||ColumnName||')';
              
          when InputData.column(i).description.type = dbms_tf.type_date 
            then
              ColumnRecord := 'to_char('||ColumnName||',''YYYY-MM-DD'')';
              
          when InputData.column(i).description.type = dbms_tf.type_timestamp
            then
              ColumnRecord := 'to_char('||ColumnName||',''YYYY-MM-DD"T"HH24:MI:SS.FF6'')';
              
            else
              null;
        end case;
 
        HeaderRecord := HeaderRecord||'||Delimiter||'||''''||ColumnName||'''';
        DataRecord := DataRecord||DelimiterRecord||ColumnRecord;
      end loop;
    
    HeaderRecord := substr(HeaderRecord, length('||Delimiter||') + 1);
    DataRecord := substr(DataRecord, length(DelimiterRecord) + 1);
   
    SQLStatement := q'[
                       select @HEADER_RECORD@ as csv_data from dual
                       union all
                       select @DATA_RECORD@ as csv_data from CSVData.InputData
                      ]';
   
    SQLStatement := replace(replace(SQLStatement, '@DATA_RECORD@', DataRecord), '@HEADER_RECORD@', HeaderRecord);
   
    return SQLStatement;
  end;
/

with city as (select *
                 from cities
                   order by 1)
                   
  select * from CSVDataTest(InputData => city);

"ID","NAME"
1,"AMSTERDAM"
2,"UTRECHT"
3,"THE HAGUE"
4,"ROTTERDAM"

with city as (select *
                 from cities
                   order by 1)
                   
  select * from CSVDataTest(InputData => city, Quote => '@');

"ID","NAME"
1,AMSTERDAM
2,UTRECHT
3,THE HAGUE
4,ROTTERDAM