如何使用Oracle的BFILE

如何使用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 ::

　　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