Dbms_lob Bfile

[hamhey]

Dear all,
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.

Thanks hamhey

[hamhey]

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:
------------------------------------
1.
create table lobtab (
lob_id number(2),
lob_file BFILE)
/

2.
create directory lobdir as '/home/oracle/lob_dir'
/
3.
insert into lobtab
values (1, BFILENAME('LOBDIR', 'ShowLetter.pdf'))
/
4.
Procedure to find the name fo the directory and the file name:
create or replace procedure getNameBfile is
Dir_Name varchar2(30);
File_Name varchar2(30);
Lob_Loc BFILE;
begin
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));
end getNameBfile;
/

-----------------------------------------

Any idea ?

//hamhey