0

I wrote a servlet program to upload an image to Oracle table. I am using Oracle 10g version.

I created the table using 

create table insertimage( image BLOB);

The table was created successfully. My servlet shows image uploaded successfully. Even when I delete using 

delete from insertimage;

it shows successful row deletion.

But when I try to see the table content using 

select * from insertimage;

it gives me 

ORA-00932: inconsistent datatypes: expected NUMBER got BLOB error.

So how can I check if the image has been successfully uploaded or not directly in the Oracle table?

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Mistu4u
  • 5,132
  • 15
  • 53
  • 91
  • No you can't view the `BLOB` data on the SQL prompt in Oracle. It contains a huge amount of binary data (Binary Large OBject). – Lion Jul 31 '12 at 05:52
  • So how can I view it? I heard third party PL/SQL developer applications can enable us to view image directly. Is it true? If it is,then please suggest one. – Mistu4u Jul 31 '12 at 05:58
  • 1
    You may ***not*** need to do so. You should just convert that BLOB data to an actual image in your Servlet/JSP/JSF or whatever front-end you're using and display it on the browser. Is there any need to query this BLOB type on the Oracle prompt directly? (In MySql, you can do so and it displays a huge amount of binary data). Additionally, storing images into the database in the form of a BLOB type is not recommended at all. You should store all of the images into a directory and its relevant path into the database table. – Lion Jul 31 '12 at 06:05
  • Actually I already tried to do so and facing problems. You can view it [here](http://stackoverflow.com/questions/11724624/image-is-not-displayed-from-oracle-table-using-servlet) – Mistu4u Jul 31 '12 at 06:08
  • It's not the way to upload and retrieve images using Servlet (or JSP, JSTL/EL, JSF) (Observing your previous [question](http://stackoverflow.com/questions/11724624/image-is-not-displayed-from-oracle-table-using-servlet)). You need to study more about how to upload and retrieve images. See [this](http://stackoverflow.com/questions/2422468/how-to-upload-files-to-server-using-jsp-servlet/) question. You need to use "Apache Common File Upload" API. See [this](http://balusc.blogspot.in/2007/11/multipartfilter.html) blog where you can also get this API. – Lion Jul 31 '12 at 06:29
  • The upload part I have done successfully. Since some rows are deleted when deleting from `insertimage` table, I am pretty sure upload has been done successfully. What is my problem is to fetch the image from database and display in the browser. And by the way, @Lion you told **storing images into the database in the form of a BLOB type is not recommended at all. You should store all of the images into a directory and its relevant path into the database table** , but I thought **database** is more _secure_ to store objects rather than using a directory!! – Mistu4u Jul 31 '12 at 06:33
  • The reason is obvious. When you convert an image into binary data, it occupies a large amount of data that can occupy a huge space in your database that can cause performance bottleneck. Additionally, you have to convert an image into binary type and again (while retrieving) into the original picture that incurs a huge amount of cost. Almost no projects use the BLOB type to store images into a database. – Lion Jul 31 '12 at 07:01
  • @Lion - an image will be binary data anyway, so why would it be converted? And it will use the same amount of space in the database as it would in a filesystem (near enough), unless you have some form of replication; and if you do then you're probably replicating the filesystem too. I agree you might be using (/wasting) a lot of dedicated DB resources to read the data when the web app layer could just as easily read it straight off disk, but I'm not sure size and format are inherently an issue. – Alex Poole Jul 31 '12 at 14:33

1 Answers1

0

There are lot of third party IDEs which you can use - a good free one would be Oracle's own SQL Developer

Double click on the table from the object tree. On the "Data" tab, click the "..." button with the BLOB column & then click the "View as Image" checkbox

Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134