Bfile and LOBs
Bfile – Binary file (text, sound, image or video) is stored outside the database. The locator to that file is stored in the database. Bfile data is read only for oracle.
LOB – BLOB and CLOB are the tow types of LOBs. B is binary and C is character data. LOB files are stored inside the database. Since the files are inside db, they can be manipulated thorough oracle. A max of 4GB data can be stored in a BLOB or CLOB data type.
Directory is a non-schema object owned by “SYS” user. It is used for managing the operating system files (that are external to oracle). Directory object will help to write data into external file or read data from an external file.
BfileName function – It initializes a Bfile column to point to an external file. Use the BfileName function as part of an Insert statement to initialize a Bfile column by associating it with a physical file in the server file system. A Bfile can be initialized to NULL and updated later by using the BFILENAME function.
FileOpen -- To open an external file.
LOADFROMFILE – To load Bfile data into an internal LOB.
Empty_CLOB – It is a special function, which can be used in Insert or Update statement of SQL DML to initialize a NULL.
FileClose – To close an external file
Example of Reading data from a Bfile and writing it in Oracle
Step 1- There is file mytextfile.txt in C:\MyData path. The mytextfile.txt has some sample text.
Step 2 – A user should have create any directory and read directory privileges. These privileges are taken from SYS user.
Step 3 - Create directory
Step 4 – Create a table mytable with two fields file_id and file_data
Create table mytable
(file_id number,
file_data clob);
create directory external_file as 'c:\mydata';
Step 5 – A procedure will take file_id and filename from user and transfer data of that file into the table mytable.
CREATE OR REPLACE PROCEDURE insertclob (p_file_id number, filename VARCHAR2)
AS
v_clob CLOB;
v_bfile BFILE;
BEGIN
INSERT INTO mytable VALUES (p_file_id, empty_clob() );
COMMIT;
v_bfile:=BFILENAME('EXTERNAL_FILE11',filename);
select file_data INTO v_clob FROM mytable WHERE file_id=p_file_id FOR UPDATE;
DBMS_LOB.FILEOPEN(v_bfile);
DBMS_LOB.LOADFROMFILE(v_clob,v_bfile,DBMS_LOB.GETLENGTH(v_bfile));
DBMS_LOB.FILECLOSE(v_bfile);
END;
/
Now execute the procedure by providing file id and file name
exec insertclob(1,'mytextfile.txt');
The data of mytextfle.txt has been transferred in the file_data column
(Note – In a session, the number of BFILEs that can be opened in one session is limited by the parameter session_max_open_files and total number of files that can be open is limited by the parameter max_open_files. These parameters are to be setted in the init.ora file
session_max_open_files=10
max_open_files=20)
The DBMS_LOB.READ Procedure
Reads data from LOB starting at specified offset
DBMS_LOB.READ(loc, num, offset, buff)
Where
loc is locator of the LOB
num is number of bytes/characters to be read
offset is offset in bytes/characters from the start of the LOB
buff is output buffer for the read operation
Example – To retrieve a part of data from the first record’s file_data column
declare
v_clob clob;
num number;
offset number;
vstore varchar2(20);
begin
num := 7;
offset :=1;
select file_data into v_clob
from mytable
where file_id = 1;
DBMS_LOB.READ(v_clob,num,offset,vstore);
dbms_output.put_line(vstore);
end;
/
The DBMS_LOB.GETLENGTH Function
Gets the length of the LOB value
DBMS_LOB.GETLENGTH(loc)
Where
loc is locator of the LOB
Example – To get the length of the file_data
declare
loc clob;
len number;
Begin
select file_data into loc from mytable
where file_id = 1;
len := DBMS_LOB.GETLENGTH(loc);
dbms_output.put_line(len);
end;
/
The DBMS_LOB.WRITE Procedure
Writes data to the LOB from specified offset
DBMS_LOB.WRITE(loc, num, offset, buff)
Where
loc is locator of the LOB
num is number of bytes/characters to write
offset is offset in bytes/characters from the start of the LOB for write operation
buff is input buffer for the write operation
Example of Write and getLength function –
--DBMS_LOB.Write and getLength Functions
-- To add some text to CLob.
-- It is going to update the original clob
--Syntax DBMS_LOB.Write(Source, Length of new text, From which character or point to be added, New Text)
Declare
loc clob;
num number;
offset number;
vstore varchar2(80);
lenlob number;
vlen number;
Begin
-- In the Select statement For Update clause is must, so that the row gets locked.
select file_data into loc from mytable where file_id=1 for update;
-- To get the total number of characters in a lob.
-- Use getlength function of DBMS_LOB
lenlob := DBMS_LOB.getLength(loc);
-- vstore is having the additional text
vstore := 'This is new line';
-- Now the length of the new text to be added should also be known since that value is one
vlen := Length(vstore); -- Length of the new text in vstore variable
offset := lenlob 1; -- Due to 1 in offset the text starts from --one space next
DBMS_LOB.Write(loc,vlen,offset,vstore);
end;
/
exec insertclob(2,'mytextfile.txt');
Example1of Erase method –
DBMS_LOB.Erase
Syntax - DBMS_LOB.Erase(Source, No. of characters to erase, Position from where it has to erase)
To remove some text from clob column
Removing the first 5 characters of file_data column for file_id 2
Declare
loc clob;
num number;
offset number;
Begin
num := 5;
offset := 1;
select file_data into loc from mytable
where file_id = 1;
DBMS_LOB.Erase(loc,num,offset);
End;
/
Discussion about this article
| Author | Body |
Rahul
8/29/2009 8:55 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|