By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,602 Members | 1,533 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,602 IT Pros & Developers. It's quick & easy.

retrieving BLOB from Oracle

P: n/a
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...b;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

Dec 1 '06 #1
Share this Question
Share on Google+
9 Replies


P: n/a
anybody? anybody know how to retrieve a binary file out of an Oracle
BLOB column via a proc? c'mon, somebody has to know.....

thanks,
matt

Dec 4 '06 #2

P: n/a
If nobody here seems to have an answer for you, maybe you could
post this to an Oracle group?

Sorry I can't help you.
Robin S.
----------------------------------
<ma**@mailinator.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
anybody? anybody know how to retrieve a binary file out of an Oracle
BLOB column via a proc? c'mon, somebody has to know.....

thanks,
matt

Dec 4 '06 #3

P: n/a
RobinS wrote:
If nobody here seems to have an answer for you, maybe you could
post this to an Oracle group?
yeah thats my next stop. i just figured since this app-code (.NET)
dependant, and not really an Oracle issue, that the .NET groups would
be most on-topic.

thanks,
matt

Dec 4 '06 #4

P: n/a
....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

Dec 4 '06 #5

P: n/a
nil

ma**@mailinator.com wrote:
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...b;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

hey can u tell me how can i save the image in the oracle in blob column
and retrieve from that?thanks in advance...
nil

Dec 5 '06 #6

P: n/a

ma**@mailinator.com wrote:
...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!
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

Dec 5 '06 #7

P: n/a
nil wrote:
hey can u tell me how can i save the image in the oracle in blob column
and retrieve from that?thanks in advance...
nil
well, first follow the link in my OP. if youre using the ms
dataprovider (System.Data.OracleClient), and not ODP.NET (oracle's data
provider) then its how you do the blob shuffle.

getting the file *out* was much simpler for me:

//conn & command
OracleConnection conn = new
OracleConnection(ConfigurationSettings.AppSettings["connStr"]);
OracleCommand command = new OracleCommand("Foo.GetFileByID", conn);
command.CommandType = CommandType.StoredProcedure;

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

//do it
DataSet ds = new DataSet();
OracleDataAdapter da = OracleClient.OracleDataAdapter(command);
da.Fill(ds);

//get file contents from returned data
byte[] fileContents = (byte[])row["FileContents"];

Response.BinaryWrite(fileContents );
matt

Dec 5 '06 #8

P: n/a
nil

ma**@mailinator.com wrote:
nil wrote:
hey can u tell me how can i save the image in the oracle in blob column
and retrieve from that?thanks in advance...
nil

well, first follow the link in my OP. if youre using the ms
dataprovider (System.Data.OracleClient), and not ODP.NET (oracle's data
provider) then its how you do the blob shuffle.

getting the file *out* was much simpler for me:

//conn & command
OracleConnection conn = new
OracleConnection(ConfigurationSettings.AppSettings["connStr"]);
OracleCommand command = new OracleCommand("Foo.GetFileByID", conn);
command.CommandType = CommandType.StoredProcedure;

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

//do it
DataSet ds = new DataSet();
OracleDataAdapter da = OracleClient.OracleDataAdapter(command);
da.Fill(ds);

//get file contents from returned data
byte[] fileContents = (byte[])row["FileContents"];

Response.BinaryWrite(fileContents );
matt
should i follow the same thing when i want to retrieve image from the
database and what if i am using oracle's provider like msdaora.1.....
so is there any way to do for this provider....thanks a lot for reply

Dec 6 '06 #9

P: n/a
nil wrote:
should i follow the same thing when i want to retrieve image from the
database and what if i am using oracle's provider like msdaora.1.....
so is there any way to do for this provider....thanks a lot for reply
dunno, dude. research time.

Dec 6 '06 #10

This discussion thread is closed

Replies have been disabled for this discussion.