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

如何使用Oracle的BFILE

2013-11-13 15:57:55  來源: Oracle 
如何使用Oracle的BFILE
創建相應的directory
使用具有足夠權限的用處創建directory具體參考:Using Create directory & UTL_FILE in Oracle
create or replace directory BFILE_DIR as
/home/oracle/bfiletest;

  [oracle@ts bfiletest]$ sqlplus / as sysdba

  SQL*Plus: Release Production on Mon Jan ::

  Copyright (c) Oracle Corporation  All rights reserved

  Connected to:
Oraclei Enterprise Edition Release Production
With the Partitioning OLAP and Oracle Data Mining options
JServer Release Production

  SQL> show parameter utl_file_dir   ***************開始已經廢棄了這個參數

  NAME                                 TYPE        VALUE

utl_file_dir                         string
SQL>
SQL> create or replace directory BFILE_DIR as
    /home/oracle/bfiletest;

  Directory created

  Elapsed: ::
SQL>
SQL> col DIRECTORY_PATH for a
SQL> select * from dba_directories;

  OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH

SYS                            MEDIA_DIR                      /oracle/product//demo/schema/product_media/
SYS                            LOG_FILE_DIR                   /oracle/admin/TSMISC/create/
SYS                            DATA_FILE_DIR                  /oracle/product//demo/schema/sales_history/
SYS                            KU$_STYLESHEET_DIR             /oracle/product//rdbms/xml/xsl
SYS                            BFILE_DIR                      /home/oracle/bfiletest

  Elapsed: ::
SQL>

  給相應的用戶授權
grant read on directory BFILE_DIR  to lunar;

  SQL> grant read on directory BFILE_DIR  to lunar;

  Grant succeeded

  Elapsed: ::
SQL>
 

  檢查相應的文件是否存在
host ls l /home/oracle/bfiletest/bfiletest_filetxt

  SQL> host ls l /home/oracle/bfiletest/bfiletest_filetxt
rwrr    root     root          Oct   /home/oracle/bfiletest/bfiletest_filetxt

  SQL>
 

  數據操作
BFILENAME函數的語法如下:BFILENAME(directoryfilename)
該函數用以返回一個BFILE文件位置指針指針和文件系統上的LOB binary文件相關聯
directory 是路徑名通過create directory方式創建 filename 是文件系統上的文件名稱
在你在SQLPL/SQL或者DBMS_LOG包或者OCI中使用BFILENAME函數之前你必須創建相應的directory並且關聯相應的物理文件
以下是一個示例:
  CREATE DIRECTORY media_dir AS /demo/schema/product_media;
  create table lunar_test (product_id number ad_id number ad_graphic bfile );
  INSERT INTO print_media (product_id ad_id ad_graphic)
     VALUES ( bfilename(MEDIA_DIR modem_comp_adgif));
  參考:Oraclei SQL Reference Release () Part Number A

  再例如
  SQL> connect lunar/lunar
  create table lunar_test (id number bfiles bfile );
 
  insert into lunar_test values ( bfilename ( BFILE_DIR bfiletest_filetxt ) );
  
  SQL> connect lunar/lunar
  Connected
  SQL> create table lunar_test (id number bfiles bfile );
 
  Table created
 
  Elapsed: ::
  SQL> insert into lunar_test values ( bfilename ( BFILE_DIR bfiletest_filetxt ) );
 
  row created
 
  Elapsed: ::
  SQL> commit;
 
  Commit complete
 
  Elapsed: ::
  SQL>

  declare                                                                           
  fhandle utl_filefile_type;                                                     
begin                                                                             
  fhandle := utl_filefopen(BFILE_DIR lunartesttxt w);                  
  utl_fileput_line(fhandle aaa);                            
  utl_fileput_line(fhandle bbb);                            
  utl_filefclose(fhandle);                                                       
end;                                                                              
/                                                                                 

  declare                                                                       
  fhandle   utl_filefile_type;                                               
  fp_buffer varchar();                                                   
begin                                                                         
  fhandle := utl_filefopen (BFILE_DIRlunartesttxt 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;      


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