How to use Oracle BFILE

Welcome to the Oracle community forum and interact with 2 million technical staff to learn how to use Oracle's BFILE1. create a corresponding directory. Use a directory with sufficient permissions to create a directory. For details, see usingcreatedirectoryutl_fileinoraclecreateorreplacedirectorybfile_dirashome1_l.

Welcome to the Oracle community forum and interact with 2 million technical staff> go to how to use Oracle's BFILE 1. create a directory to create a directory with sufficient permissions. For details, see Using Create directory UTL_FILE in Oracle create or replace directory BFILE_DIR as '/home/CMDL.

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

How to use Oracle BFILE
1. Create the corresponding directory
Create a directory with sufficient permissions. For more information, see Using Create directory & UTL_FILE in Oracle.
Create or replace directory BFILE_DIR
'/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,200 2, Oracle Corporation. All rights reserved.

Connected:
Oracle9i Enterprise Edition Release 9.2.0.6.0-Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0-Production

SQL> show parameter utl_file_dir ***************** 9.2, this parameter has been discarded

NAME TYPE VALUE
-----------------------------------------------------------------------------
Utl_file_dir string
SQL>
SQL> create or replace directory BFILE_DIR
2'/home/oracle/bfiletest ';

Directory created.

Elapsed: 00:00:00. 05
SQL>
SQL> col DIRECTORY_PATH for a50
SQL> 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/xsl
SYS BFILE_DIR/home/oracle/bfiletest

Elapsed: 00:00:00. 01
SQL>

2. Authorize the corresponding user
Grant read on directory BFILE_DIR to lunar;

SQL> grant read on directory BFILE_DIR to lunar;

Grant succeeded.

Elapsed: 00:00:00. 04
SQL>

3. Check whether the corresponding file exists.
Host ls-l/home/oracle/bfiletest/bfiletest_file.txt

SQL> host ls-l/home/oracle/bfiletest/bfiletest_file.txt
-Rw-r -- 1 root 349 Oct 31 2003/home/oracle/bfiletest/bfiletest_file.txt

SQL>

4. Data Operations
The syntax of the BFILENAME function is as follows: BFILENAME ('Directory ', 'filename ')
This function is used to return a BFILE position pointer. the pointer is associated with the LOB binary file in the file system.
'Directory 'is the path name and is created using create directory. 'Filename' is the file name on the file system.
Before using the BFILENAME function in SQL, PL/SQL, DBMS_LOG, or OCI, you must create the corresponding directory and associate it with the corresponding physical files.
The following is an example:
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,310 01, bfilename ('Media _ dir', 'modem_comp_ad.gif '));
Reference: Oracle9i SQL Reference Release (9.2) Part Number A96540-02

For example:
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;