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

Oracle讀寫文件bfilename

2013-11-13 15:41:49  來源: 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

      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 oneeygle 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_fileSYS

  BDUMP_DIR                      /opt/oracle/admin/conner/bdumpSYS                                                         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_fileSYS

  BDUMP_DIR                      /opt/oracle/admin/conner/bdump

  create or replace directory USER_DIR as E:\PLSQL\\;

  DECLARE

  v_content VARCHAR();

  v_bfile BFILE;

  amount INT;

  offset INT :=;

  BEGIN

  v_bfile := bfilename(USER_DIRtestTXT); 注意這裡的 User_dir 對應上面已經創建好啦的目錄

  amount :=DBMS_LOBgetlength(v_bfile);

  DBMS_LOBOPEN(v_bfile);

  DBMS_LOBREAD(v_bfileamountoffsetv_content);

  DBMS_LOBclose(v_bfile);

  DBMS_OUTPUTPUT_LINE(v_content);

  END;


From:http://tw.wingwit.com/Article/program/Oracle/201311/17151.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.