 |
|
Oracle External LOB BFILE Data types
Oracle Database Tips by Donald Burleson |
External datatypes can be of any format, text,
graphic, GIF, TIFF, MPEG, MPEG2, JPEG, etc. When read into the
database they will have to be moved into a compatible format (BLOB,
CLOB or NCLOB).
The BFILE datatype is used to act as a pointer or
locator for the actual external data files. The BFILE datatype is a
two part locator, the first part is the file name and the second
part is the internal alias for the full path directory where the
files reside. The directory alias is defined with the CREATE
DIRECTORY command:
CREATE OR REPLACE DIRECTORY gif_dir AS '/usr/graphics/gif';
Once a directory alias is established, users are
granted access through an object grant on the directory alias via
the GRANT command:
GRANT READ ON DIRECTORY gif_dir TO system;
BFILEs can be up to four gigabytes in length or up
to the maximum size of files for your system whichever is smaller.
BFILEs are capable of piece-wise read only. To perform any
manipulations of BFILE data the BFILE locator is used to open the
BFILE physical file and the DBMS_LOB package is then used to read
the contents into an internal LOB. The Server Image Cartridge
provides a method to write LOBs back out to physical files if you
have the cartridge installed. Alternatively, JAVA or another
language interface can be used to write the LOB data back to the
system files.
The BFILENAME () function must be called as part of
SQL INSERT to initialize a BFILE column or attribute for a
particular row by associating it with a physical file in the
server's filesystem.
The DIRECTORY object represented by the
directory_alias parameter to the BFILENAME() function must already
be defined using SQL DDL before this function is called in a SQL DML
statement or a PL/SQL program.
However, BFILENAME() does not
validate privileges on this DIRECTORY object, or check if the
physical directory that the DIRECTORY object represents
actually exists. These checks are performed only during file access
using the BFILE locator that is initialized by the BFILENAME()
function.
You can use BFILENAME() as part of a SQL INSERT
and UPDATE statement to initialize a BFILE column. You can
also use it to initialize a BFILE locator variable in a PL/SQL
program, and use that locator for file operations. However, if the
corresponding directory alias and/or filename does not exist, then
PL/SQL DBMS_LOB routines that use this variable will generate
errors.
Oracle BFILENAME tips
BFILENAME
function returns a BFILE locator that is associated with a physical
LOB binary file on the server file system. The syntax of the function
is given as below.
BFILENAME('directory', 'filename')
·
‘directory’ argument is a database object that serves as an alias for
a full path name on the server file system where the files are
actually located.
·
‘filename’
is the name of the file in the server file system.
·
The
directory object has to be created and a BFILE value has to be
associated with a physical file before they are used as arguments to
BFILENAME in a SQL statement, PL/SQL block, DBMS_LOG package or OCI
operation.
This
function can be used in two ways:
·
To
initialize a BFILE
column in a DML statement.
·
To access
BFILE data by assigning a value to the BFILE locator in a programmatic
interface.
The
directory argument is case sensitive. The directory object name
specified in the function and in the data dictionary must exactly be
the same. The filename argument must be specified according to the
case and punctuation conventions of the operating system. The
following SQL statement depicts the usage of the BFILENAME function.
CREATE DIRECTORY Screenshots_Dir AS '/Screenshots';
Directory Created.
create table Screenshots (imgid number(3), screenimage BFILE);
Table created.
insert into screenshots values (101,
BFILENAME('Screenshots_Dir','scrimg1.jpeg'));
1
row created.
select BFILENAME('Screenshots_Dir','scrimg1.jpeg') from dual;
BFILENAME('SCREENSHOTS_DIR','SCRIMG1.JPEG')
--------------------------------------------------------------------------------
bfilename('Screenshots_Dir', 'scrimg1.jpeg')
Large Object Functions
Large Object
(LOB) is a built-in data type in Oracle. The data types Binary Large
Object (BLOB), Character
Large Object (CLOB),
National Character Large Object (NCLOB) and BFILE
can be used to store large and
unstructured data such as text, image, video, and spatial data. LOB
columns contain LOB locators that can refer to internal (in the
database) or external (outside the database) LOB values.
The LOB locator is returned by selecting a LOB from a table.
This locator is used for the
DBMS_LOB package
and Oracle Call Interface (OCI) operations on LOBs.
The large object functions usually operate on the LOB data.
 |
Advanced PL/SQL Programming
The Definitive Reference
Boobal Ganesan
Retail Price $49.95 /
£39.95
Buy Direct from
Publisher for 30% off! $32.95
|
| |
Buy it Now! |
|
and include the URL for the page.
