RE: BLOB or BFILE?

Hamid
   I think you mentioned the critical words in my reply -- "In theory". Just
a wild thought, not even sure it would be possible. Probably somebody on the
list has used UTL_FILE to create a file. But then you would have to figure
out how to set that file up as a BFILE. 

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Hamid Alavi
Sent: Thursday, June 03, 2004 2:51 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: BLOB or BFILE?


I have to keep the data for 6 month and each night we have to run an
incremental export which I think could be slow.
BTW you mentioned using staging table then write it on database server by
using UTL_FILE can we do this at some other then removing it from staging
table or not?
Do you have a sample code for using UTL_FILE 

-----Original Message-----
From: DENNIS WILLIAMS [mailto:DWILLIAMS@xxxxxxxxxxxxx]
Sent: Thursday, June 03, 2004 12:28 PM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: RE: BLOB or BFILE?


Hamid
   Hopefully someone who has used the BFILE will respond to your posting. My
suspicion is that a lack of replies indicates a lack of usage. 
   1) The command to send a BLOB over an ODBC connection is SELECT. Just
plain old SQL. The key part is that your application must be written to
handle the data. For example, if the BLOB is a photograph, then it won't do
any good to select the BLOB in a SQL*Plus on a terminal. You'll just get
garbage on your screen.
   2) I think you are correct that over an ODBC connection it will be
difficult for you to create BFILE objects. In theory you could probably save
the object to a staging table in Oracle (as a BLOB) and then use UTL_FILE to
write the physical file. This hardly seems worth the work.
   How long do you need to keep the objects? Any performance criteria?
Without testing, 40-meg. seems pretty large for a BLOB. But then you're
going to use about the same amount of disk space whether you store the
object in a BLOB or BFILE.

Dennis Williams
DBA
Lifetouch, Inc.
dwilliams@xxxxxxxxxxxxx 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Hamid Alavi
Sent: Thursday, June 03, 2004 12:04 PM
To: 'Oracle-L (E-mail)
Subject: BLOB or BFILE?


List,

I need your opinion on the case which I am working on?

I have a application which connect to Oracle database(9.2.0.5) via ODBC
connection , thru this connection application send a File(between 5 to 40 M)
average 20 times a night then these file will be later downloaded from the
user thru the application I have two Options as follows:

1) Store the file as BLOB into the table using store procedure to write the
BLOB to the table BUT I don't know how do I have to send it back to the
application via ODBC?

2) Using BFILE and store the FILE in Database server in this case do I have
to COPY the file into the directory which I have define in oracle if yes How
do I have to COPY the file into the directory via ODBC?

Finally which of these two way will be more efficient?

Thanks for your help appreciate.


Hamid Alavi

Office           :  818-737-0526
Cell phone  :  818-416-5095

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: