使用 UTL_FILE匯出TAB和逗號分割資料

DECLARE
  row_result varchar2(4000);
  selectsql  varchar2(4000);
  qrycursor  SYS_REFCURSOR;
  txt_handle UTL_FILE.file_type;
BEGIN
  selectsql  := 'select USER_ID||chr(9)||EXTERNAL_ID||chr(9)||ACCOUNT_ID||chr(9)||CERTIFY_STATUS||chr(9)||USER_STATUS||chr(9)||RUNTIME_STATUS||chr(9)||USER_NAME||chr(9)||REAL_NAME||chr(9)||COUNTRY||chr(9)||GENDER||chr(9)||PROFESSION||chr(9)||ADDRESS||chr(9)||PHONE||chr(9)||MOBILE||chr(9)||EMAIL||chr(9)||CERT_TYPE||chr(9)||CERT_NO||chr(9)||LICENSE_VALID_TIME||chr(9)||REGISTER_FROM||chr(9)||ORIGIN_REGISTER_FROM||chr(9)||RAW_ADD_TIME||chr(9)||RAW_UPDATE_TIME||chr(9)||BANK_CARD_NO||chr(9)||BANK_CARD_TYPE||chr(9)||NAME||chr(9)||BANK_CERT_NO from yhcheck.til_per';
  txt_handle := UTL_FILE.FOPEN('TESTDIR', 'b.txt', 'w',32767);
  open qrycursor for selectsql;
  loop
    fetch qrycursor
      into row_result;
    exit when qrycursor%notfound;
    UTL_FILE.PUT_LINE(txt_handle, row_result);
  end loop;
  --關閉遊標    
  close qrycursor;
  UTL_FILE.FCLOSE(txt_handle);
end;

逗號分隔
DECLARE
  row_result varchar2(4000);
  selectsql  varchar2(4000);
  qrycursor  SYS_REFCURSOR;
  txt_handle UTL_FILE.file_type;
BEGIN
  selectsql  := 'select id || '','' || name || '','' || dti
  from testdump
 where name = ''gaopeng''
   and dti > to_date(''2015-03-17 00:00:00'', ''yyyy-mm-dd hh24:mi:ss'')';
  txt_handle := UTL_FILE.FOPEN('DATA_PUMP_DIR', 'b.txt', 'w', 32767);
  open qrycursor for selectsql;
  loop
    fetch qrycursor
      into row_result;
    exit when qrycursor%notfound;
    UTL_FILE.PUT_LINE(txt_handle, row_result);
  end loop;
  --關閉遊標    
  close qrycursor;
  UTL_FILE.FCLOSE(txt_handle);
end;


注意點 1:'w'可以更改為'a'為追加
         2:32767為每行的寬度，預設為1024
         3:TESTDIR為大寫是 CREATE DIRECTORY建立的目錄
         4: chr(9) 為TAB分割