Posted on 2009-01-10 11:48
詩特林 閱讀(35227)
評論(3) 編輯 收藏 所屬分類:
Oracle
Create directory讓我們可以在Oracle數據庫中靈活的對文件進行讀寫操作，極大的提高了Oracle的易用性和可擴展性。
其語法為:
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
本案例具體創建如下:
create or replace directory exp_dir as '/tmp';
|
目錄創建以后，就可以把讀寫權限授予特定用戶，具體語法如下:
GRANT READ[,WRITE] ON DIRECTORY directory TO username;
例如:
grant read, write on directory exp_dir to eygle;
|
此時用戶eygle就擁有了對該目錄的讀寫權限。
讓我們看一個簡單的測試:
SQL> create or replace directory UTL_FILE_DIR as '/opt/oracle/utl_file';
Directory created.
SQL> declare
2 fhandle utl_file.file_type;
3 begin
4 fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'w');
5 utl_file.put_line(fhandle , 'eygle test write one');
6 utl_file.put_line(fhandle , 'eygle test write two');
7 utl_file.fclose(fhandle);
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> !
[oracle@jumper 9.2.0]$ more /opt/oracle/utl_file/example.txt
eygle test write one
eygle test write two
[oracle@jumper 9.2.0]$
|
類似的我們可以通過utl_file來讀取文件:
SQL> declare
2 fhandle utl_file.file_type;
3 fp_buffer varchar2(4000);
4 begin
5 fhandle := utl_file.fopen ('UTL_FILE_DIR','example.txt', 'R');
6
7 utl_file.get_line (fhandle , fp_buffer );
8 dbms_output.put_line(fp_buffer );
9 utl_file.get_line (fhandle , fp_buffer );
10 dbms_output.put_line(fp_buffer );
11 utl_file.fclose(fhandle);
12 end;
13 /
eygle test write one
eygle test write two
PL/SQL procedure successfully completed.
|
可以查詢dba_directories查看所有directory.
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS UTL_FILE_DIR /opt/oracle/utl_file
SYS BDUMP_DIR /opt/oracle/admin/conner/bdump
SYS EXP_DIR /opt/oracle/utl_file
|
可以使用drop directory刪除這些路徑.
SQL> drop directory exp_dir;
Directory dropped
SQL> select * from dba_directories;
OWNER DIRECTORY_NAME DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS UTL_FILE_DIR /opt/oracle/utl_file
SYS BDUMP_DIR /opt/oracle/admin/conner/bdump
|