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)
