熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

用Create directory-對文件靈活讀寫

2013-11-13 16:07:11  來源: Oracle 

  Create directory讓我們可以在Oracle數據庫中靈活的對文件進行讀寫操作極大的提高了Oracle的易用性和可擴展性
  
  其語法為:
  
  create or replace directory exp_dir as /tmp;
  
  目錄創建以後就可以把讀寫權限授予特定用戶例如:
  
  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
     fhandle utl_filefile_type;
    begin
     fhandle := utl_filefopen(UTL_FILE_DIR exampletxt w);
     utl_fileput_line(fhandle eygle test write one);
     utl_fileput_line(fhandle eygle test write two);
     utl_filefclose(fhandle);
    end;
    /
  
  PL/SQL procedure successfully completed
  
  SQL> !
  [oracle@jumper ]$ more /opt/oracle/utl_file/exampletxt
  eygle test write one
  eygle test write two
  [oracle@jumper ]$
  
  類似的我們可以通過utl_file來讀取文件:
  
  SQL> declare
     fhandle  utl_filefile_type;
     fp_buffer varchar();
    begin
     fhandle := utl_filefopen (UTL_FILE_DIRexampletxt R);
   
     utl_fileget_line (fhandle fp_buffer );
     dbms_outputput_line(fp_buffer );
     utl_fileget_line (fhandle fp_buffer );
     dbms_outputput_line(fp_buffer );
     utl_filefclose(fhandle);
    end;
    /
  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
From:http://tw.wingwit.com/Article/program/Oracle/201311/17844.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.