-
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';
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|