DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: how to get bfile's directory and filename

  1. #1
    Join Date
    Jan 2001
    Posts
    191

    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

  2. #2
    Join Date
    Jan 2001
    Posts
    191
    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
  •  


Click Here to Expand Forum to Full Width