I have some tables that have LOB BFILES out of line.
How can I use DBMS_LOB to see the directory and file name in combination wih select of the rows in the table including BFILE.
Using DBMS_LOB.FILEGETNAME you should have both the file name and the directory the file resides.
Any idea to get guery like select for rows including column that have pointer to BIFILE.
I used the following procedure to print out the locatin directory and file name without no result. It seems that the output of DBMS_LOB.FILEGETNAME is not a char format:
create table lobtab (
create directory lobdir as '/home/oracle/lob_dir'
insert into lobtab
values (1, BFILENAME('LOBDIR', 'ShowLetter.pdf'))
Procedure to find the name fo the directory and the file name:
create or replace procedure getNameBfile is
select LOB_FILE into Lob_Loc from LOBTAB where LOB_ID=1 ;
DBMS_LOB.FILEGETNAME(Lob_Loc, Dir_Name, File_Name);
-- DBMS_OUTPUT.PUT_LINE( to_char(Dir_Name));
-- DBMS_OUTPUT.PUT_LINE( to_char(File_Name));