oracle bfile 优点,如何使用Oracle的BFILE

如何使用Oracle的BFILE以下文字资料是由(历史新知网www.lishixinzhi.com)小编为大家搜集整理后发布的内容，让我们赶快一起来看一下吧！

如何使用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:Oracle i Enterprise Edition Release ProductionWith the Partitioning OLAP and Oracle Data Mining optionsJServer Release Production

SQL> show parameter utl_file_dir   *************** 开始 已经废弃了这个参数

NAME                                 TYPE        VALUE utl_file_dir                         stringSQL> 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/xslSYS                            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_file txt

SQL> host ls l /home/oracle/bfiletest/bfiletest_file txt rw r r     root     root          Oct   /home/oracle/bfiletest/bfiletest_file txt

SQL>

数据操作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 ( bfilename( MEDIA_DIR modem_p_ad gif ));  参考:Oracle i 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_file txt ) );     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_file txt ) );    row created     Elapsed: : :   SQL> mit;    Commit plete     Elapsed: : :   SQL>

declare                                                                              fhandle utl_file file_type;                                                      begin                                                                                fhandle := utl_file fopen( BFILE_DIR lunartest txt w );                     utl_file put_line(fhandle aaa );                               utl_file put_line(fhandle bbb );                               utl_file fclose(fhandle);                                                        end;                                                                               / lishixinzhi/Article/program/Oracle/201311/17596

分页：123