The usage of bfile type in Oracle

This article introduces the knowledge of "the usage of bfile type in Oracle". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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

When using bfile, you need to use a directory object of Oracle, which simply maps an operating system directory to a "string" or a name in the database.

Create a table with 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'

Insert content into the 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, otherwise an error will occur. (it is normal when inserting, but there will be errors 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 viewing in plsql developer, the first insert is normal, and the second insert shows Value Error

The amount of space bfile takes up on disk varies, depending on the length of the directory object name and file name. In general, bfile consumes about 20 bytes of overhead.

Unlike other lob data, bfile data is not "read consistent". Because bfile is managed outside the database, bfile contact references will be reflected in the results, no matter what happens on the file, so reading the same bfile over and over again may produce different results.

This is the end of the introduction to the usage of bfile types in Oracle. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!