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

Thread: Dbms_lob Bfile

  1. #1
    Join Date
    Feb 2003
    Location
    Tehran/Iran
    Posts
    94

    Dbms_lob Bfile

    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

  2. #2
    Join Date
    Feb 2003
    Location
    Tehran/Iran
    Posts
    94
    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
    Last edited by hamhey; 01-08-2004 at 02:42 AM.

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