Re: Q: BFILE::FILEOPEN() in Oracle8

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 06 Jul 1999 18:01:08 GMT
Message-ID: <37864432.25679364@newshost.us.oracle.com>

A copy of this was sent to Yan Zhou <yzhouy_at_yahoo.com> (if that email address didn't require changing) On Tue, 06 Jul 1999 13:42:30 -0400, you wrote:

>Hi there,
>
> I have installed Oracle8i on a NT server and trying to get BFILE work.
>The problem is that I can not open a file as BFILE.I think there is
>something
>wrong with my "create or replace directory" and "bfilename()", but could
>
>not figure that out.
>
>Let us say there is a file d:\mydir\pictures\test.gif and I try to open
>it through
>BFile using package DBMS_LOB. The following is the simplifed code:
>
>declare
>l_bfile bfile;
>
>create or replace directory MY_FILES as 'd:\mydir\pictures';
>
>l_bfile := bfilename('MY_FILES', 'test.gif');
>dbms_lob.fileopen(l_bfile);
>
>Any hint is greatly appreciated.
>
> Yan Zhou
> yzhou_at_lexmark.com
>

Here is an example. Don't put the SQL DDL "create or replace directory..." inside your plsql block -- it is a standalone statement.

drop table demo
/

create table demo

( id            int primary key,
  theBlob       blob

)
/

create or replace directory my_files as '/export/home/tkyte/public_html' /

declare

        l_blob  blob;
        l_bfile bfile;
begin
        insert into demo values ( 1, empty_blob() ) 
        returning theBlob into l_blob;

        l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
        dbms_lob.fileopen( l_bfile );

        dbms_lob.loadfromfile( l_blob, l_bfile, 
                               dbms_lob.getlength( l_bfile ) );

        dbms_lob.fileclose( l_bfile );

end;
/

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jul 06 1999 - 13:01:08 CDT