Here is an example using select-into to process 1 file. You can easily amend this to being a for-loop.

SQL> drop table t purge;

Table dropped.

SQL> create table t ( x clob );

Table created.

SQL>
SQL> declare
  2    c clob;
  3  begin
  4    insert into t values ( empty_clob())
  5    returning x into c;
  6
  7    c := q'{<?xml version='1.0'  encoding='UTF8' ?>}<RESULTS>}';
  8    loop
  9      c := c || '<ROW><COLUMN NAME="EMPNO"><![CDATA[7369]]></COLUMN><COLUMN NAME="ENAME"><![CDATA[SMITH]]></COLUMN><COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN><COLUMN NAME="MGR"><![CDATA[7902]]></COLUMN></ROW>';
 10      exit when length(c) > 90000;
 11    end loop;
 12    c := c || '</RESULTS>';
 13
 14    update t set x = c;
 15    commit;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL> select length(x), x from t;

 LENGTH(X) X
---------- --------------------------------------------------------------------------------
     90190 <?xml version='1.0'  encoding='UTF8' ?>}<RESULTS><ROW><COLUMN NAME="EMPNO"><![CD

1 row selected.

SQL>
SQL> DECLARE
  2    Lv_cursor PLS_INTEGER;
  3    Lv_file UTL_FILE.file_type;
  4    P_SubstrVal VARCHAR2(4000) ;
  5    lv_file_name VARCHAR2 (100):='testing.csv';
  6    P_Directory VARCHAR2(101) :='TEMP';
  7    crlf VARCHAR2(2) := chr(10)||chr(13);
  8    l_columnValClob CLOB;
  9    P_offset NUMBER :=1;
 10    P_amount NUMBER := 3000;
 11    l_clobLen NUMBER :=0;
 12  BEGIN
 13    Lv_cursor := DBMS_SQL.open_cursor;
 14    SELECT x
 15    INTO l_columnValClob
 16    FROM t;
 17
 18    l_clobLen := DBMS_LOB.GETLENGTH(l_columnValClob);
 19    Lv_file := utl_file.fopen(P_Directory,lv_file_name,'wb');
 20    WHILE P_offset < l_clobLen
 21    LOOP
 22      P_SubstrVal := dbms_lob.substr(l_columnValClob,P_amount,P_offset);
 23      utl_file.put_raw(Lv_file,utl_raw.cast_to_raw(P_SubstrVal));
 24      P_offset:=P_offset+P_amount;
 25    END LOOP;
 26    UTL_FILE.fclose(Lv_file);
 27  END ;
 28  /

PL/SQL procedure successfully completed.

SQL>
SQL>

Now that will write the files to a folder that the *database* can access. Options to get it your local machine could be a shared folder that both database and clients can access.

If you need it exclusively to a local drive, you'd need to either

- write something yourself (eg Java)
- use SQL Developer, which has a "export as separate files" option