(.NET) retrieving BLOB from Oracle via proc

From: <matt_at_mailinator.com>
Date: 4 Dec 2006 14:04:07 -0800
Message-ID: <1165269847.783153.66300@80g2000cwy.googlegroups.com>

hello,

sorry for the slightly OT post, but not getting any hits from the ADO.NET groups.

i am trying to insert & retrieve binary files w/ Oracle (no need for a discussion on *that*!). it would appear that for files over 33k, this ADO.NET technique must be used for insertion:

http://support.microsoft.com/default.aspx?scid=kb;en-us;322796

...in which you fill an OracleLob object and pass it into your BLOB column.

now i have to retrieve my blob file, and response it to the user. i imagine the process is something like: 1) get data out of db, 2) convert to working format 3) response to client.

however, im running into hitches. not sure what is wrong.. the byte array of the retrieved file matches the original's size-on-disk, and when its saved via the response to disk, it also matches the orig. but -- opening it is impossible, corrupted. opening in notepad yields an empty file, devoid of the normal binary garbage characters.

...was wondering if anyone had ADO.NET code or techniques for this?

my snippet:

/conn & command
OracleConnection conn = new OracleConnection(connStr); OracleCommand command = new
OracleCommand("COFE.GetInspectionReportByID", conn); command.CommandType = CommandType.StoredProcedure;

//params

command.Parameters.Add("p_fileID", OracleType.Number).Value = reportID;
command.Parameters.Add("cur_results", OracleType.Cursor).Direction =
ParameterDirection.Output; //output

OracleLob blob = null;

//fill blob

conn.Open();
OracleDataReader dr = command.ExecuteReader();

if (dr.Read()) //has row

    blob = dr.GetOracleLob(5);

byte[] byteArray = (byte[])blob.Value;

conn.Close();

//response (kitchen sink for tests)

Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=" +

report.FileName);
Response.BufferOutput = true;

Response.BinaryWrite(byteArray);
Response.End();    

any help appreciated,
matt Received on Mon Dec 04 2006 - 16:04:07 CST