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
|
http://m.tkk7.com/cheneyfree/archive/2007/12/04/165275.html 這篇文章很不錯。
轉自：http://www.eygle.com/archives/2005/04/using_create_di.html
---------------------------------------------------------------------------------------------------------------------------------
說人之短，乃護己之短。夸己之長，乃忌人之長。皆由存心不厚，識量太狹耳。能去此弊，可以進德，可以遠怨。
http://m.tkk7.com/szhswl
------------------------------------------------------------------------------------------------------ ----------------- ---------
posted on 2007-12-05 11:25
宋針還 閱讀(810)
評論(0) 編輯 收藏 所屬分類:
存儲過程