Re: copy a BFILE to a BLOB

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/11/18
Message-ID: <36594b06.33986089@192.86.155.100>#1/1

A copy of this was sent to vagelis Hristidis <exrist_at_cc.ece.ntua.gr> (if that email address didn't require changing) On Wed, 18 Nov 1998 23:29:39 +0200, you wrote:

>I try to copy a BFILE to a BLOB:
>
>declare
> bl1 BLOB;
> bf1 BFILE;
>begin
> select bf into bf1
> from bfiles
> where id=23;
> select bl into bl1
> from imtable
> where id=11
> for update;
> DBMS_LOB.LOADFROMFILE(bl1,bf1,1000000);
> commit;
>end;
>/
>
>but I get:
>declare
>*
>ERROR at line 1:
>ORA-22285: non-existent directory or file for KOLFLBCTX2 operation
>ORA-06512: at "SYS.DBMS_LOB", line 414
>ORA-06512: at line 16
>
>Any ideas?

As the error message says, the DIRECTORY to which the bfile refers (when you create a BFILE, you specfy the DIRECTORY of the file and the NAME of the file), does not exist -- you must create it. Consider the following example:

SQL> create table bfiles

  2  ( id            number,
  3    bf            bfile

  4 )
  5 /
Table created.

SQL> create table blobs

  2  ( id            number,
  3    theBlob       blob

  4 )
  5 /
Table created.

SQL> insert into blobs values ( 23, empty_blob() ); 1 row created.
SQL> insert into bfiles values
  2 ( 11, bfilename( 'A_DIRECTORY_THAT_DOES_NOT_EXIST', 'foo.dat' ) ); 1 row created.

SQL> declare

  2          l_bfile bfile;
  3          l_blob  blob;

  4 begin
  5

  5          select bf into l_bfile
  6            from bfiles
  7           where id = 11;
  8  
  8          select theBlob
  9            into l_blob
 10            from blobs
 11           where id = 23;
 12  
 12          dbms_lob.loadFromFile( l_blob, l_bfile, 1000000 );

 13 end;
 14 /
declare
*
ERROR at line 1:

ORA-22285: non-existent directory or file for KOLFLBCTX2 operation
ORA-06512: at "SYS.DBMS_LOB", line 414
ORA-06512: at line 12

I would need to create the Oracle directory A_DIRECTORY_THAT_DOES_NOT_EXIST for this to work, for example:

create or replace directory A_DIRECTORY_THAT_DOES_NOT_EXIST as '/export/home/tkyte/public_html';

>Thanks in advance.
>Please mail to me
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
 
Anti-Anti Spam Msg: if you want an answer emailed to you, 
you have to make it easy to get email to you.  Any bounced
email will be treated the same way i treat SPAM-- I delete it.

Received on Wed Nov 18 1998 - 00:00:00 CST