hello,

im doing my first ASP.NET app that inserts & retrieves files from Oracle (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 my files are larger than 33k, it seemed 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 of working code for doing this? for retrieval, heres my snippet:
 
 
//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; //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

If you need on ASP.NET I would suggest to start using dataAdapters.

Add New Item (DataSet) into the project and you'll be able to drag and drop tables from Oracle and create functions. Great to learn and create a database layer

let me rephrase that; this isnt my "first ASP.NET project" this is my first asp.net project "that inserts & retrieves files from Oracle".

i am well versed in ADO.NET, and i do indeed have a DA layer. for my projects, we use procs. this is outside my jurisdiction.

what i am still in need of is confirmation of how folks are retrieving binary files out of Oracle. code snippets are preferred.

thanks,
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.

if somebody could help me confirm the insert/retrieve techniques, id likely be golden.


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 blobbing, but i hadnt *filled* it! so the insert worked, the retrieval worked, but the file contents were simply empty.

 
matt 

can you elaborate more on what you did? Thanks.