Oracle 11g export table to text file

env: Windows Server 2008R2
        Oracle 11g


在SQL Server環境中可以透過SSMS把資料庫的table匯出為檔案或是透過sqlcmd來產生文字檔，Oracle環境下常用的工具是Toad.
若要透過指令則是要利用"utl_file"達到匯出為文字檔，並且以逗號做分隔

1.首先我們要建立"utl_file_dir"的directory
command:
create or replace directory utl_file_dir as 'C:\temp';

SQL> create or replace directory utl_file_dir as 'C:\temp';

已建立目錄.

2.檢查Oracle parameter "utl_file_dir"設定值

command:

select value from v$parameter where name='utl_file_dir';

SQL> select value from v$parameter where name='utl_file_dir';

VALUE

--------------------------------------------------------------------------------

3.在spfile設定"utl_file_dir"的directory，並重啟Oracle instancet，重啟後再次檢查Oracle parameter "utl_file_dir"設定值
command1:
alter system set utl_file_dir='c:\temp' scope=spfile;

command2:
shutdown immediate

command3:
startup

command4:
select value from v$parameter where name='utl_file_dir';

SQL> alter system set utl_file_dir='c:\temp' scope=spfile;

已更改系統.

SQL> shutdown immediate

資料庫關閉.

資料庫已卸載.

已關閉 ORACLE 執行處理.

SQL> startup

ORACLE 執行處理已啟動.

Total System Global Area  855982080 bytes

Fixed Size                  2286032 bytes

Variable Size             520097328 bytes

Database Buffers          327155712 bytes

Redo Buffers                6443008 bytes

資料庫已掛載.

資料庫已開啟.

SQL> select value from v$parameter where name='utl_file_dir';

VALUE

--------------------------------------------------------------------------------

c:\temp

4.建立測試表格，並顯示測試表格schema內容
command1:
create table dbm.employee_file as select * from hr.employees;

command2:
select count(*) from dbm.employee_file;

command3:
desc dbm.employee_file;

SQL> create table dbm.employee_file as select * from hr.employees;

已建立表格.

SQL> select count(*) from dbm.employee_file;

  COUNT(*)

----------

       107

SQL> desc dbm.employee_file;

 名稱                                     空值?    類型

 ---------------------------------------- -------- ---------------------------

 EMPLOYEE_ID                                       NUMBER(6)

 FIRST_NAME                                        VARCHAR2(20)

 LAST_NAME                                NOT NULL VARCHAR2(25)

 EMAIL                                    NOT NULL VARCHAR2(25)

 PHONE_NUMBER                                      VARCHAR2(20)

 HIRE_DATE                                NOT NULL DATE

 JOB_ID                                   NOT NULL VARCHAR2(10)

 SALARY                                            NUMBER(8,2)

 COMMISSION_PCT                                    NUMBER(2,2)

 MANAGER_ID                                        NUMBER(6)

 DEPARTMENT_ID                                     NUMBER(4)

4.執行指令進行匯出文字檔
請留意"v_file_dir varchar2(20)"長度是否比"utl_file_dir"設定的名稱長或相同，否則執會出現錯誤。

DECLARE

row_result varchar2(1024);

selectsql varchar2(1024);

qrycursor SYS_REFCURSOR;

v_file_dir varchar2(20);

v_curr_date varchar2(14);

v_file_title varchar2(50);

v_file_name varchar2(37);

txt_handle UTL_FILE.file_type;

BEGIN

  v_file_dir := 'UTL_FILE_DIR';

  v_curr_date := to_char(sysdate, 'yyyymmddhh24miss');

  v_file_name := 'dba_users' || '_' || v_curr_date || '.txt';

  selectsql := 'select OWNER||'',''||OBJECT_NAME||'',''||SUBOBJECT_NAME||'',''||OBJECT_ID||'',''||DATA_OBJECT_ID from dba_objects ';

  txt_handle := UTL_FILE.FOPEN(v_file_dir,v_file_name,'w');

  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;