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

Just adding to the last post.

From personal experience.....

You also have to remember about directory names you use. I'm not sure what OS you are using. If its Windows and you are using a mapped drive, Oracle may not necessarily know about it and you will receive errors when you use the virtual directory for DBMS_LOB and BFILES (or UTL_FILE for that matter). Probably best to use the UNC file format of \\machine\share\directory since that is universal. If you insist on using mapped drives, you may need to run Oracle as an Operating System user as opposed to the default Oracle user.

Check metalink for more info on the above

HTH, Mark

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<3c8d9d32.63176479_at_news.saix.net>...
> 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? ;-)
Received on Tue Mar 12 2002 - 15:35:28 CET