Re: BFILE & BLOB question (Binary file from the client machine)

noelp1_at_hotmail.com (Noel P) wrote:

[I do not appreciate crosspostings - all other newsgroups snipped from this reply posting]

>Now, I have read a lot on BLOB/BFILE etc. If I use a BFILE, it talks
>about creating logical directory and stuff, but not clear on whether
>the file should be in a directory on the server or client. 

On the server.

You create a directory on the server. You put the files there.

In Oracle, you create a directory object pointing to that directory with files. IMPORTANT. Make sure that the operating system user in which Oracle runs, has read (optionally write) permission on that directory and its files.

The BFILE column contains a file pointer/handle to the file in that directory object. The contents of that file is NOT stored in the BFILE column - it is just a filename handle.

To insert a file handle into a bfile column: INSERT INTO foo
 ( picture_name, jpeg_file )
VALUES
( 'kitty', BFILENAME( 'JPEG_DIR', 'kitty.jpg' ) );

Where JPEG_DIR is the directory object.

>2. Now, how can I extract the file from the database to a local >directory, again on the client.

The simplest way is via a PL/SQL wrapper containing code like this:
// you select the BFILE column into the variable fil
   

// you change get the physical filename for the BFILE variable
  dbms_lob.FileGetName( fil, dname, fname );

// you can check if it exists

  i := dbms_lob.FileExists( fil );
  if i = 0 then

     raise_application_error( blah blah file does not exist )   end if;

// you can open the file

  dbms_lob.FileOpen(fil, dbms_lob.file_readonly);

// you can check the size of the file

  i := dbms_lob.getlength(fil);

// you can read the contents of the file into a
// buffer using a start pointer and number of
// bytes to read (ie. just like a BLOCKREAD() call
// in C++, Pascal, whatever)

  dbms_lob.read(fil, i, 1, buf);

// you can close the file

  dbms_lob.fileclose(fil);

// and you can type cast the raw buffer read into
// a varchar2 - assuming of course you have read
// a text file (just be wary of the max size of
// a varchar2 here)

  memo := sys.utl_raw.cast_to_varchar2(buf);

Now you know how BFILES work. Right? ;-)

--
Billy