Bfile of LOB in Oracle

The bfile type is a lob type, and the bfile type is just a pointer to the previous file on the operating system to provide read-only access to the operating system files. Maintenance outside the database, not part of the database.

When using BFILE, an Oracle directory object is required, and the directory object simply maps an operating system directory to a "string" or a name in the database.

Create a Table bfile_test,os_file field type bfile

Sql> CREATE TABLE bfile_test (ID int primary key,os_file bfile);

Create a directory for storing files

sql> Create or replace directory My_dir as '/home/oracle/photo ';

Inserting content into a table

sql> INSERT into bfile_test values (1,bfilename (' My_dir ', ' 1.jpg '));

Sql> commit;

You can see the size of the specified file

Sql> Select Dbms_lob.getlength (os_file) from Bfile_test;

Note that when inserting, the My_dir must be capitalized, or an error will occur. (Normal when inserted, error when querying)

sql> INSERT into bfile_test values (2,bfilename (' My_dir ', ' 1.jpg '))

Sql> commit;

Sql> Select Dbms_lob.getlength (os_file) from Bfile_test;

ERROR:

Ora-22285:non-existent directory or file for getlength operation

Ora-06512:at "SYS. Dbms_lob ", line 850

Sql> select * from Bfile_test;

When viewed in Plsql developer, the first insert is normal, the second Insert view displays the value Error

The amount of space that bfile occupies on the disk depends on the directory object name and the length of the file name, which in general bfile consumes approximately 20 bytes of overhead.

Unlike other LOB data, bfile data is not "read-consistent", and because Bfile is managed outside the database, the bfile contact reference, regardless of what happens on the file, is reflected in the resulting results, so repeated reading of the same bfile may produce different results.

This article is from "The girl said" blog, please be sure to keep this source http://sugarlovecxq.blog.51cto.com/6707742/1689342

Bfile of LOB in Oracle