RE: BLOB or BFILE?

From: Hamid Alavi <hamid.alavi_at_quovadx.com>
Date: Thu, 3 Jun 2004 14:50:41 -0500
Message-ID: <C398496DF88AD711908C00065BEEE32CFE99DE@dfwmail.quovadx.com>

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_at_LIFETOUCH.COM] Sent: Thursday, June 03, 2004 12:28 PM
To: 'oracle-l_at_freelists.org'
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.

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.
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_at_lifetouch.com

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]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:

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?
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_at_freelists.org 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_at_freelists.org

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_at_freelists.org

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

Received on Thu Jun 03 2004 - 14:39:06 CDT