retrieving BLOB from Oracle

4 Dec 2006 7:11 PM

matt

hello,

for the first time i have to insert & retrieve files from an Oracle
datasource (no need for a discussion on *that*!).

i learned first-hand of the somewhat awkward technique for inserting
binary data into an Oracle BLOB column via ADO.NET. since the files are
larger than 33k, it seems i had to use this technique:

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 (via a proc in my case).

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. can anyone
post snippets how this is done?

fyi, heres what ive tried:

//conn & command
OracleConnection conn = new
OracleConnection(ConfigurationSettings.AppSettings["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;

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();

....however, when excel loads the file, it says its an "unrecognizable
format". opening it in notepad, its empty. *but*, right-clicking the
file, its size on disk matches the pre-databased version exactly!

any idea what im missing?

thanks!
matt

4 Dec 2006 9:52 PM

matt

....i tested this w/ another file format, .PDF. same thing -- the byte
array of the retrieved file matches the original's size-on-disk. when
its saved via the response to disk, it also matches the orig.

however, opening it is impossible. opening it in notepad yields an
empty file, devoid of the normal binary garbage characters.

matt

5 Dec 2006 5:44 PM

matt

figured it out. it had nothing to w/ my retrieval code.

the files were coming out of oracle w/ the correct byte size, but empty
in notepad, because i inserted them incorrectly.

for the insert, i had *sized* the byte array prior to blob-and-insert,
but i hadnt *filled* it! so the insert worked, the retrieval worked,
but the file contents were simply empty.

matt