MANIPULATING BFILE
/*Grant the directory creation prioviledge to scott */
GRANT CREATE ANY DIRECTORY TO SCOTT;
GRANT DROP ANY DIRECTORY TO SCOTT;
/*create a file into scott which hold the bfile*/
CREATE TABLE HOLD(NAME BFILE);
/*directory information are stored in the table */
select * from DBA_DIRECTORIES;
select * from ALL_DBA_DIRECTORIES;
/*CREATE A PHYSICAL DIRECTORY IN E:/ARKA/ABC */
/*CREATE DIRECTORY*/
CREATE DIRECTORY ABC AS 'E:/ARKA/ABC';
/*GRANTING FOR PRIVILEDGE TO READ DIRECTORY OBJECT TO THE USER*/
GRANT READ ON DIRECTORY ABC TO PUBLIC;
/*ADDING BFILE*/
insert into hold values(bfilename('abc','cat.wmv'));
insert into hold values(bfilename('abc','kyo2.gif'));
insert into hold values(bfilename('abc','moustache.jpg'));
insert into hold values(bfilename('abc','10roses.pps'));
/**/
SELECT * FROM HOLD;
/*it returns error because sql* plus cannot open any gif or....*/
MANIPULATING EXTERNAL LOB
-----------------------------------------------------/*LOB */
/*create the directory in the physical path*/
create or replace directory LOB_SOURCE as 'e:/arka/abc';
/*create a physical file in that named "lobfile.txt"*/
/*create a table which hold the clob file*/
create table lob_tab ( the_lob clob );
/*procedure to fecth the file length*/
declare
v_lob clob;
l_bfile bfile;
buf raw(32767);
vc varchar2(32767);
amt number;
v_offset number := 1;
begin
l_bfile := bfilename( 'LOB_SOURCE', 'lobfile.txt' );
insert into lob_tab (the_lob) values ( empty_clob() )
returning the_lob into v_lob;
amt := dbms_lob.getlength( l_bfile );
dbms_lob.fileopen( l_bfile ,dbms_lob.file_readonly);
dbms_lob.read(l_bfile,amt,v_offset,buf);
vc := utl_raw.cast_to_varchar2(buf);
dbms_lob.writeappend(v_lob,amt,vc);
dbms_output.put_line('File length is: '||dbms_lob.getlength( l_bfile ));
dbms_output.put_line('Loaded length is: '||dbms_lob.getlength(v_lob));
dbms_lob.fileclose( l_bfile );
end;
/
-------------------------------------------------------------------------------------------------------------------------------
MANIPULATING INTERNAL LOB
/*CREATING A TABLE CARRYING A LOB VALUE*/
create table lob_exa(name varchar2(10),resume clob);
/*INSERTING DATA INTOLOB */
insert into lob_exa values('arka','B.tech engineer passout from west bengal university of technology. in 2007 . hometown Chandernagore');
/*DECLARING A CLOB WITHOUT ASSIGNING A VALUE INTO IT*/
insert into lob_exa values('Dibyendu',empty_clob());
/*ASSIGNING DATA TO EMPTY_CLOB()*/
UPDATE LOB_EXA
SET RESUME='B.COM FROM RISHI BANKIM COLLEGE. HOMETOWN CHUCHURA'
WHERE NAME= 'Dibyendu';
/*APPNEDING TO LOB(FOR INSERTING DBMS_LOB.WRITE(LOBLOC,AMOUNT,OFFSET,TEXT)*/
DECLARE
LOBLOC CLOB;
TEXT VARCHAR2(32767):='NATIONALITY INDIAN';
OFFSET INTEGER;
AMOUNT NUMBER;
BEGIN
SELECT RESUME INTO LOBLOC
FROM LOB_EXA WHERE NAME='Dibyendu' FOR UPDATE;
OFFSET:=DBMS_LOB.GETLENGTH(LOBLOC)+2;
AMOUNT:=LENGTH(TEXT);
DBMS_LOB.WRITEAPPEND(LOBLOC,AMOUNT,TEXT);
COMMIT;
END;
