how to get bfile's directory and filename

this is demo table
SQL> desc pm.PRINT_MEDIA;
Name Null? Type
----------------------------------------- -------- ----------------------------
PRODUCT_ID NOT NULL NUMBER(6)
AD_ID NOT NULL NUMBER(6)
AD_COMPOSITE BLOB
AD_SOURCETEXT CLOB
AD_FINALTEXT CLOB
AD_FLTEXTN NCLOB
AD_TEXTDOCS_NTAB PM.TEXTDOC_TAB
AD_PHOTO BLOB
AD_GRAPHIC BINARY FILE LOB
AD_HEADER PM.ADHEADER_TYP


how can I get dirctory and file name for field AD_GRAPHIC.

which function I can use.

bfilename is used to insert data into tables.
In Oracle/PLSQL, the bfilename function returns a BFILE locator for a physical LOB binary file.

The syntax for the bfilename function is:

bfilename( 'directory', 'filename' )

now, I have file locator, AD_GRAPHIC, how can I get directory and filename?

Thanks.
xyz2000

ok. I find the way to do it.

from web site http://www.oracle-training.cc/10g_154.htm


SQL> CREATE FUNCTION get_dir_name (bf BFILE) RETURN VARCHAR2 IS
DIR_ALIAS VARCHAR2(255);
FILE_NAME VARCHAR2(255);
BEGIN
IF bf is NULL
THEN
RETURN NULL;
ELSE
DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
RETURN dir_alias;
END IF;
END;
/
2 3 4 5 6 7 8 9 10 11 12 13
Function created.

SQL> CREATE FUNCTION get_file_name (bf BFILE) RETURN VARCHAR2 is
dir_alias VARCHAR2(255);
file_name VARCHAR2(255);
BEGIN
IF bf is NULL
THEN
RETURN NULL;
ELSE
DBMS_LOB.FILEGETNAME (bf, dir_alias, file_name);
RETURN file_name;
END IF;
END;
/
2 3 4 5 6 7 8 9 10 11 12 13
Function created.




SQL> select get_dir_name (AD_GRAPHIC) from pm.PRINT_MEDIA;

GET_DIR_NAME(AD_GRAPHIC)
--------------------------------------------------------------------------------
MEDIA_DIR
MEDIA_DIR
MEDIA_DIR
MEDIA_DIR

SQL> select get_file_name (AD_GRAPHIC) from pm.PRINT_MEDIA;

GET_FILE_NAME(AD_GRAPHIC)
--------------------------------------------------------------------------------
monitor.jpg
mousepad.jpg
keyboard.jpg
modem.jpg


select DIRECTORY_PATH from dba_directories where DIRECTORY_NAME='MEDIA_DIR';