473,372 Members | 1,381 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

retrieving BLOB from Oracle

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
9 7936
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
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
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
....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
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

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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: Chris Fink | last post by:
I have setup an Oracle table which contains a blob field. How do I insert data into this field using C# and ADO.net?
0
by: Utilisateur1 | last post by:
Hello, I've a problem retrieving a blob column from oracle. I'm using oledbadapter to fill a dataset like that oledataadap.fill(ds,"query"); I'm also using ColumnMapping to have more friendly...
4
by: Nau | last post by:
How can I display BLOB(Oracle) data in asp page
11
by: Peter Afonin | last post by:
Hello, I have Word documents stored in the Oracle database in the BLOB field that I need to retrieve. I've found the way of doing it: While dr.Read sName = dr("TE_PM_ATTACH_NAME") bLob =...
0
by: coony | last post by:
Hi everyone, I got an annoying thing going on. I've got an MSSQL db which is filled with different data, used by another program (T-Plan). I should read some table to import in another DB. The...
0
by: Harshadcode | last post by:
Hi, This is HARSHAD, I am creating a project in VB.NET for managing details like photos of criminals. but i am facing problems in converting a image in to filestream so that it can be inserted in...
3
by: jackliu | last post by:
Hi there, I have a problem about how to use MS Access insert an image to BLOB field in AS/400? Could anyone provide me VB code for inserting and retrieving BLOB data? Thanks in advance. ...
1
by: sana krishna | last post by:
Pls Help me-------------- I use ASP(C#.NET) and oracle. When i retrive image from database .it display error message... System.InvalidCastException: Unable to cast object of type 'System.DBNull'...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.