如何使用Oracle的BFILE

如何使用Oracle的BFILE1.创造相映的directory运用具备充满权力的用途创造directory,简直参考:using create directory & utl_file in oracle create or replace directory bfile_dir as '/home/oracle/bfiletest';

[oracle@ts01 bfiletest]$ sqlplus '/ as sysdba'

sql*plus: release 9.2.0.6.0 - production on mon jan 23 10:54:17 2006

copyright (c) 1982, 2002, oracle corporation.  all rights reserved.

connected to:oracle9i enterprise edition release 9.2.0.6.0 - productionwith the partitioning, olap and oracle data mining optionsjserver release 9.2.0.6.0 - production

sql> show parameter utl_file_dir   ***************9.2发端，仍旧废除了这个参数

name                                 type        value------------------------------------ ----------- ------------------------------utl_file_dir                         stringsql> sql> create or replace directory bfile_dir as   2  '/home/oracle/bfiletest';

directory created.

elapsed: 00:00:00.05sql>sql> col directory_path for a50sql> select * from dba_directories;

owner                          directory_name                 directory_path------------------------------ ------------------------------ --------------------------------------------------sys                            media_dir                      /oracle/product/920/demo/schema/product_media/sys                            log_file_dir                   /oracle/admin/tsmisc02/create/sys                            data_file_dir                  /oracle/product/920/demo/schema/sales_history/sys                            ku$_stylesheet_dir             /oracle/product/920/rdbms/xml/xslsys                            bfile_dir                      /home/oracle/bfiletest

elapsed: 00:00:00.01sql>

2.给相映的用户受权grant read on directory bfile_dir  to lunar;

sql> grant read on directory bfile_dir  to lunar;

grant succeeded.

elapsed: 00:00:00.04sql> 

3.查看相映的文献能否生存host ls -l /home/oracle/bfiletest/bfiletest_file.txt

sql> host ls -l /home/oracle/bfiletest/bfiletest_file.txt-rw-r--r--    1 root     root          349 oct 31  2003 /home/oracle/bfiletest/bfiletest_file.txt

sql>  

4.数据操纵bfilename因变量的语法如次:bfilename('directory','filename')该因变量用以归来一个bfile文献场所南针，南针和文献体例上的lob binary文献关系联。'directory' 是路途名，经过create directory办法创造。 'filename' 是文献体例上的文献称呼在你在sql,pl/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 (3000, 31001, bfilename('media_dir', 'modem_comp_ad.gif'));  参考:oracle9i sql reference release (9.2) part number a96540-02

再比方：  sql> connect lunar/lunar  create table lunar_test (id number, bfiles bfile );     insert into lunar_test values ( 1, bfilename ( 'bfile_dir', 'bfiletest_file.txt' ) );     sql> connect lunar/lunar  connected.  sql> create table lunar_test (id number, bfiles bfile );     table created.    elapsed: 00:00:00.03  sql> insert into lunar_test values ( 1, bfilename ( 'bfile_dir', 'bfiletest_file.txt' ) );    1 row created.    elapsed: 00:00:00.00  sql> commit;    commit complete.    elapsed: 00:00:00.00  sql>

declare                                                                              fhandle utl_file.file_type;                                                      begin                                                                                fhandle := utl_file.fopen('bfile_dir', 'lunartest1.txt', 'w');                     utl_file.put_line(fhandle , 'aaa');                               utl_file.put_line(fhandle , 'bbb');                               utl_file.fclose(fhandle);                                                        end;                                                                               /                                                                                 

declare                                                                          fhandle   utl_file.file_type;                                                  fp_buffer varchar2(4000);                                                    begin                                                                            fhandle := utl_file.fopen ('bfile_dir','lunartest1.txt', 'r');                                                                                                utl_file.get_line (fhandle , fp_buffer );                                      dbms_output.put_line(fp_buffer );                                              utl_file.get_line (fhandle , fp_buffer );                                      dbms_output.put_line(fp_buffer );                                              utl_file.fclose(fhandle);                                                    end;