Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle External LOB BFILE Data types


Oracle Database Tips by Donald BurlesonConsulting

 

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 Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2016

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.