Skip to Main Content
  • Questions
  • How to store images into the database through forms

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, mallika.

Asked: May 24, 2000 - 1:32 pm UTC

Last updated: October 01, 2015 - 11:29 am UTC

Version: version 6.0

Viewed 50K+ times! This question is

You Asked

Dear tom
I am having one image table at the back end.I want to insert images into it through front end and at the same time i want to display the sizes of the images simultaneously.
Do we need any Built-ins like READ_IMAGE_FILE&WRITE_IMAGE_FILE?
Can u explain in detail the procedure for doing that?
Thanks
Mallika

and Tom said...

This is pretty easy. The answer is READ_IMAGE_FILE. The will read a file stored on the machine with the form is executing. The image will become 'visible' on screen. When the user saves into the database -- the image will go into the database.

WRITE_IMAGE_FILE is what you would use to export an image that exists in the database to the users machine. They would query a record up -- get to the image they wanted and you would give them a button or something to "export" the image. The when-button-pressed trigger would invoke write_image_file to save the contents of the image to disk.


As a demonstration -- I did the following:


scott@8i> create table image_table ( filename varchar2(255) primary key, image long raw );

Table created.


I created a table that had a filename as the primary key and a image column to hold a picture. I then created a default block on this table -- using all of the defaults (just pressed NEXT in the data block wizard and layout wizard, always selecting both columns). So, I ended up with a form that had a IMAGE_TABLE block with columns FILENAME and IMAGE.

On this block I put a button. I coded a "when-button-pressed" trigger as follows:


read_image_file( :image_table.filename,
'JPG', 'image_table.image' );


I then ran this form. I typed into the filename field "c:\temp\tkyte.jpg" (thats a file i had on my machine) and pressed the button. The contents of the file were displayed on screen. I then did "action/save" to put the record into the database and commit. I then did a "query/enter" to clear the block out and then "query/execute" to query up the record I just put in there.

thats it...




Rating

  (19 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

how to insert a image into database

kareem, July 18, 2001 - 3:06 am UTC

>sir,
>how to insert the image into oracle8.0 database
>and how to retrive?
>so please clarify my doubt as early as possible
>with command.
>please give me procedure how to do.
>>Thanking you sir,
>your truely,
>kareem
>kareem2020@yahoo.com
>



Short and Sweet

Steve C., February 14, 2002 - 7:39 am UTC

Fantastic, I've been looking for something to do this exact same thing.

READ_IMAGE_FILE problems ...

Vladas, June 04, 2003 - 7:02 am UTC

I found one, but very bad thing, if in Oracle form I use READ_IMAGE_FILE for image insert in the table (in example foto.jpg and I don't use any compressions for this image item in my form, the Image format is JFIF) and later try to see this image, the image quality is badly than original my image. If I use BMP type of Image format the image quality is such as original but the image size too big. If I understood correctly the Oracle Forms READ_IMAGE_FILE function use some compression :(.


May be somebody know how insert image in JPEG format from Oracle forms and local PC to BLOB field in the server with image quality such as original.

Image retrieval using forms ver. 6.0

Archie Magnaye, June 24, 2003 - 9:39 pm UTC

The article came in very handy. But suppose I'd like to print that particular image, using reports ver 6.0, how do i retrieve the image from the image_table and print it on screen or on paper? Thank you and hope to hear for your prompt response..

Tom Kyte
June 25, 2003 - 11:53 am UTC

sorry, haven't used forms since 1995 myself.

try otn.oracle.com -> discussion forums, lots of forms developers out there who have probably done this (or at least tried)

How to load and display images from database

Yong, June 25, 2003 - 5:12 pm UTC

Here is my way to do so

CREATE TABLE IMAGES
(
  IMAGE_NAME  VARCHAR2(30)  primary key,
  MIME_TYPE   VARCHAR2(30),
  CONTENT     BLOB
)

CREATE OR REPLACE PACKAGE BODY IMAGE AS

PROCEDURE load(filename VARCHAR2) AS
  f_lob BFILE;
  b_lob BLOB;
  image_name VARCHAR2(30);
  mime_type VARCHAR2(30);
  dot_pos NUMBER;
BEGIN
    dot_pos := INSTR(filename,'.');
    image_name := filename; --SUBSTR(filename,1,dot_pos-1);
    mime_type := 'image/'||SUBSTR( filename,dot_pos+1,length(filename) );

    INSERT INTO images values(image_name,mime_type,empty_blob() )
           RETURN content INTO b_lob;
    f_lob := BFILENAME('SOURCE_DIR',filename);
    dbms_lob.fileopen(f_lob,dbms_lob.file_readonly);
    dbms_lob.loadfromfile(b_lob,f_lob,dbms_lob.getlength(f_lob) );
    dbms_lob.fileclose(f_lob);
    COMMIT;
END;
/*=====*/
PROCEDURE get(name varchar2) AS
  vblob     BLOB;
  mime_type VARCHAR2(30);
BEGIN
  select content,mime_type
  into vblob,mime_type
  from images
  where image_name=name;
  owa_util.mime_header(mime_type, false);
  owa_util.http_header_close;
  wpg_docload.download_file(vblob);
    exception when others then
          htp.p(sqlcode || sqlerrm);
END;
/*=====*/
END;
/

SQL> create directory source_dir as '/mydir';
if you have an image file in /mydir/toolbar.gif

SQL> exec image.load('toolbar.gif');

create procedure test as
begin
  htp.p('<img src=image.get?name=toobar.gif>');
end;
/ 

another problem

oracle, July 19, 2004 - 7:46 am UTC

I have stores image in
--employee table--
(
e_id varchar2(12);
img bolb
);

--data---
e100 e100.jpg
e200 e200.jpg

another table

e_salary
(
e_id varchar2 (12);
salary number(10)
);
e_salary contain a 'image item'
so that when some one enters
employee id it will display his/her picture .

How I can do that using form 6i?



Tom Kyte
July 19, 2004 - 8:00 am UTC

i haven't used forms since 1995 -- suggest you try otn.oracle.com, there are discussion forums there and one of them is centered around forms/developer

A reader, July 19, 2004 - 10:02 am UTC

Once the image is loaded, Oracle will query and display the image on the form. You don't have to do anything else. Try it out..


can you help

oracle, July 21, 2004 - 2:22 am UTC

"a reader' can you give a example.
It will be very helpful for me .

Tom Kyte
July 21, 2004 - 7:52 am UTC

they are saying "create default block on table, it'll just happen"

Different blob format?

Alex, February 16, 2006 - 9:18 am UTC

I have about 2000 images in database (8.1.7) stored through forms application. I can see them in forms applications, and download them using pl/sql as well. But, I can not see them using java swing client (application build using bc4j, JDeveloper). However, images uploaded using java swing client are visible by both: forms and java swing.

Is there something specific with blobs and forms?

Tom Kyte
February 16, 2006 - 12:10 pm UTC

define what you mean by "not see them", they are just data in the database?

A reader, February 16, 2006 - 11:13 pm UTC

Its such a same after numerous postings by Tom mentioning error message or a small demo still people dont get it, too many post mentioning "does not work" :-(




blobs, forms, jdeveloper

Alex, February 17, 2006 - 3:57 am UTC

Well, I start to believe that blobs are not just data in database :)

Gifs are stored in blob column through Forms application. Fields in this column are not empty, I can see that using pl/sql (blob fields could be exported in regular gif files through PL/SQL Developer for example), and I can display them through Forms application.

I have problem with displaying gifs in java/swing application. To display gifs I use JUImageControl, and it doesn't work with ones stored by Forms application. If I use JTextField instead of JUImageControl, I can see that bc4j returns data (not very meaningful character/byte array representing contents of a gif file).

If the very same files are stored through java/swing client they are visible in both swing and forms application.

In thread </code> http://forums.oracle.com/forums/thread.jspa?messageID=801811󃰓 <code>I saw that the reason for irregular blob behaviour could be its "forms background".

What is exactly problem?

Is it possible to update blob column in database somehow (reloading over 2000 gifs will be pretty uncomfortable and slow) ?

I am using JDev 9.0.5.1 build 1605, DB 8.1.7., Forms 6i.

Tom Kyte
February 17, 2006 - 1:46 pm UTC

I have no idea what JUImageControl is or does, sorry.




blobs stored through forms are different than ones stored through Java

Alex, February 24, 2006 - 2:36 am UTC

Surprise! Forms uses compression (apparantly unhandled by JUImageControl) when storing gif files into blobs. That way, same gifs are not same blobs if they are stored through Forms and Java/swing client. More about possible workaround would be soon on metalink.

DISPLAYING JPG FILE STORE IN THE DATABASE ON FORMS

Jim, December 18, 2006 - 5:08 pm UTC

I have loaded various images into the database.

Normally I can just goto the block and execute the query and the image (blob) field will appear.

There are various JPG files that do not show up and no error is given. The orginal jpg files that seem to cause the problem are from digital camera. The file has lots of extra infon on it (camera type, f-stop, iso, etc). If I take the same image and load it up in Microsoft photo editor and then re-save it (changing nothing) it loads and displays just fine.

The end users do not have time to re-save every image they wish to load into the database.

I am running: Forms Version 9.0.2.7.0
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production


Tom Kyte
December 18, 2006 - 7:11 pm UTC

you can use the forums on otn.oracle.com for forms - I haven't used it in over ten years.

Add image to Oracle 10g XE

Rahul, February 19, 2007 - 8:10 am UTC

Sir,
How do one add images onto a table in an Oracle 10g XE edition?
Thanks
Rahul

Displaying BLOBS on Oracle Forms

Frank T, November 06, 2007 - 10:13 am UTC

Our DBA has an oracle table with BLOB/image column, however, the BLOB/image column is defined as ORDIMAGE data type. I am trying to display this BLOB/image on my Form data block item of type Image.

I have queried the table blob/image column within the when-button-pressed and have the blob/image readily available in variable of type ORDIMAGE as defined in the database, but I am wondering if I can use "Read_Image_File" or some other built-in function to display this on my Form. Your assistance in this is greatly appreciated, Tom. Frank
P.S. I am using Forms 10.1.2 and Oracle Database 10.2

store image in database and call through oracle form 6i

Ganesh, December 08, 2007 - 1:57 am UTC

Dear tom I am having one image table at the back end.I want to show that image through front end at the same time in oracle form 6i.

query to save image into db

A reader, February 08, 2012 - 5:09 am UTC

HI,
I need to store image into my table...
please any one help me..
Tom Kyte
February 08, 2012 - 5:32 am UTC

given the copious, detailed information you've given me, this is about the best i can do:

http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/toc.htm

storing documents in the database

Boris, March 12, 2012 - 4:33 pm UTC

Hi Tom,

I have been asked to design the best way to manage the documents for one of our business requirements.
As per your suggestion , I will go for secure LOB to store the ocuments in the database itself rather than storing the documents in the file system, but I am a bit confused about storage and retrieval of the file from the database.

I have a table called doc_detail with the following details

create table doc_detail
(
document_id number primary key, -- system generated key
document_type varchar2(10) not null, -- doc,pdf,xls
document_content blob not null
)
lob(document_content) store as securefile slob
tablespace test_data;


Sample Data:

1 doc test.doc
2 pdf test.pdf


DB version :Oracle 11g Release 1

My queries are

1. How to load the above doc,pdf files into the oracle database without using sql loader?

2. What is the best way to do capacity planning for these documents. Let's say the average size of each file is 50k max.

3. How to download the data from the table with proper format? I heard that we can not download the documents from database without java interface. Is that true?

4. Are there significant overhead in storing the file in the database?

Could you please shed me some lights on this? It would be great if you give an example, if possible.

Thanks for excellent service to oracle community.

Boris

Tom Kyte
March 13, 2012 - 7:33 am UTC

what programming environment are you using? If you used something as simple as APEX, you wouldn't even have to create a table (we already have one for documents) and the file upload is rather trivial, you just include a button basically.

as for capacity planning - you'd have to tell us how many documents you plan on having over what period of time. Then, for storage capacity, we just multiply.

you can download documents easily - for example, click on the files tab on this page (upper right) and click on a link and start downloading files. You want to know how many lines of java code that took? Zero. In fact, it took pretty much zero lines of code - it is built in with APEX ( http://apex.oracle.com/ to test drive it, you already own it, it comes with every Oracle database)

there are no overheads as far as I'm concerned, only benefits to storing it in the database - like backup, recovery, universal access (what happens if you decide to have more than one app server and you were using file systems?), security, integrity, searchability and so on.



Asad, October 01, 2015 - 10:13 am UTC

I want to add push button in a form that call a directory local machine. as result i can upload a image in oracle database by oracle form.
Connor McDonald
October 01, 2015 - 11:29 am UTC

http://www.oracle.com/technetwork/developer-tools/forms/webutil-090641.html

is a suite of tool for Forms that allow some of the old client/server functionality to be replicated in modern versions of Forms

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library