Manipulating External LOBS (BFILES) with DBMS_LOB Package
---------------------------------------------------------
A BFILE column or attribute for a particular row or user-defined type stores a BFILE locator which can be considered as an opaque pointer to a file on the server's file system. The locator contains the directory alias, the filename, and some state information. A new DIRECTORY database item enables administering access and usage of BFILES. A DIRECTORY item specifies an alias for a directory on the server's file system.
The schema creating a directory object needs special privileges. The SQL statement below grants to user naveen the privilege to create directory objects.
grant create any directory to naveen;
Creating a directory object which maps an operating system path where the flat file is stored. The directory object name is used to map an operating system file directory to a BFILE Lob Locator.
create or replace directory "LOBMANIP" as '/home/usupport/npasumar/bulletin/';
Loading a lob locator of a BFILE using the DBMS_LOB.LOADFROMFILE
The following four steps are involved in loading a BFILE locator to map OS file. Note that ORACLE has only read-only access to BFILEs.
1) Check if the external file was opened by using the specified FILE locator using the DBMS_LOB.FILEISOPEN() function.
Syntax
FUNCTION FILEISOPEN (
file_loc IN BFILE)
RETURN INTEGER;
2) Open the external file using the DBMS_LOB.FILEOPEN() procedure.
Syntax
PROCEDURE FILEOPEN (
file_loc IN OUT BFILE,
open_mode IN BINARY_INTEGER := file_readonly);
3) Copy a part or whole of the external LOB into a Internal LOB using
the DBMS_LOB.LOADFROMFILE.
Syntax
PROCEDURE loadfromfile (
dest_lob IN OUT BLOB,
src_file IN BFILE,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1);
4) Close the BFILE that was opened via the DBMS_LOB.FILEOPEN() procedure.
Syntax
PROCEDURE FILECLOSE (
file_loc IN OUT BFILE);
Example: This program reads data from an operating system file and reads it
into a BLOB. It assumes that the directory object LOBMANIP has been
created and the directory to which it maps contains a file
'sample10.pc'.
CREATE OR REPLACE PROCEDURE load_lob IS
temp_blob BLOB;
file_on_os BFILE := bfilename('LOBMANIP', 'append.sql');
ignore INTEGER;
BEGIN
ignore := dbms_lob.fileexists(file_on_os);
IF ignore=1 THEN
ignore := dbms_lob.fileisopen(file_on_os);
IF (ignore=1) THEN
null;
ELSE
dbms_lob.fileopen(file_on_os, dbms_lob.file_readonly);
END IF;
SELECT video_clip INTO temp_blob FROM lob_store WHERE lob_id = 5 FOR UPDATE;
dbms_output.put_line('External file size is: ' ||
dbms_lob.getlength(file_on_os));
dbms_lob.loadfromfile(temp_blob,file_on_os, dbms_lob.getlength(file_on_os));
dbms_lob.fileclose(file_on_os);
dbms_output.put_line('Internal BLOB size is: ' ||
dbms_lob.getlength(temp_blob));
ELSE
dbms_output.put_line('File does not exist');
END IF;
COMMIT;
END;
SQL> exec load_lob
External file size is: 1230
Internal BLOB size is: 1230
PL/SQL procedure successfully completed.
The next example demonstrates how the data stored in an internal LOB can be written to a flat file using a combination of the built-in packages DBMS_LOB and UTL_FILE.
CREATE OR REPLACE PROCEDURE retrieve_lob IS
temp_blob BLOB;
data_buffer RAW (1);
temp_buffer VARCHAR2(1);
amount BINARY_INTEGER := 1;
position INTEGER := 1;
filehandle utl_file.file_type;
error_number NUMBER;
error_message VARCHAR2(100);
length_count INTEGER;
BEGIN
SELECT video_clip INTO temp_blob FROM lob_store WHERE lob_id = 1;
length_count := dbms_lob.getlength(temp_blob);
dbms_output.put_line('Internal LOB size is: ' || length_count);
filehandle := utl_file.fopen('/home/usupport/npasumar/bulletin',
'lob_flat.out','W');
WHILE length_count <> 0 LOOP
dbms_lob.read (temp_blob, amount, position, data_buffer);
temp_buffer := utl_raw.cast_to_varchar2(data_buffer);
utl_file.put (filehandle, temp_buffer);
position := position + 1;
length_count := length_count - 1;
data_buffer := null;
END LOOP;
dbms_output.put_line('Exit the loop');
utl_file.fclose(filehandle);
dbms_output.put_line('Close the file');
EXCEPTION
WHEN OTHERS THEN
BEGIN
error_number := sqlcode;
error_message := substr(sqlerrm ,1 ,100);
dbms_output.put_line('Error #: ' || error_number);
dbms_output.put_line('Error Message: ' || error_message);
utl_file.fclose_all;
END;
END;
Note: The utl_file package is only suitable for handling TEXT data.
SQL> exec retrieve_lob
Internal LOB size is: 1230
Exit the loop
Close the file
PL/SQL procedure successfully completed.
Comparison Between OCI Functions and DBMS_LOB PACKAGES
------------------------------------------------------
The LOBs can also be manipulated using OCI. Here is a comparison of the DBMS_LOB package procedures and the OCI functions.
OCI (ociap.h) DBMS_LOB (dbmslob.sql)
N/A DBMS_LOB.COMPARE()
N/A DBMS_LOB.INSTR()
N/A DBMS_LOB.SUBSTR()
OCILobAppend DBMS_LOB.APPEND()
OCILobAssign N/A [use Pl/SQL assign operator]
OCILobCharSetForm N/A
OCILobCharSetId N/A
OCILobCopy DBMS_LOB.COPY()
OCILobDisableBuffering N/A
OCILobEnableBuffering N/A
OCILobErase DBMS_LOB.ERASE()
OCILobFileClose DBMS_LOB.FILECLOSE()
OCILobFileCloseAll DBMS_LOB.FILECLOSEALL()
OCILobFileExists DBMS_LOB.FILEEXISTS()
OCILobFileGetName DBMS_LOB.FILEGETNAME()
OCILobFileIsOpen DBMS_LOB.FILEISOPEN()
OCILobFileOpen DBMS_LOB.FILEOPEN()
OCILobFileSetName N/A (use BFILENAME operator)
OCILobFlushBuffer N/A
OCILobGetLength DBMS_LOB.GETLENGTH()
OCILobIsEqual N/A [use Pl/SQL equal operator]
OCILobLoadFromFile DBMS_LOB.LOADFROMFILE()
OCILobLocatorIsInit N/A [always initialize]
OCILobRead DBMS_LOB.READ()
OCILobTrim DBMS_LOB.TRIM()
OCILobWrite DBMS_LOB.WRITE()
Frequently Asked Questions
--------------------------
1. What are the differences between LONG and LOB?
LONG LOB
---- ---
Single column per table Multiple columns per table
Up to 2 Gigabytes Up to 4 Gigabytes
SELECT returns data SELECT returns locator
Data stored in-line Data stored in-line or out-of-line
No object type support Support object types
Sequential access of data Random access of data
2. When can LOBs be stored in-line?
When LOB columns are created as a part of a table, they are created by default in in-line mode. In this mode, if the LOB (data+locator) < 4000 bytes, then the LOB is stored inline. If the LOB (data+locator) > 4000 bytes, then it is stored out-of-line. Inline storage of LOBs smaller than 4K can be disabled (disable storage in row). In this case, only the locator is stored in the row and the data is stored in the LOB segment.
|