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

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;