Storing and Retrieving Images / Word / Excel / PDF and Movies in Oracle Database using Forms10g

Hi all,

I have seen lot of people asking about it at OTN Forums that "How to Store Retrieve Images, Word, Excel, Pdf and Movies into Oracle using Forms".

So here i m providing easiest solution by using WEBUTIL Library.

You can download my Working example

Pre-requisites:
- Webutil setup and configuration (My old post can help u)

-In ORACLE_HOME/forms/server/webutil.cfg file the parameter transfer.database.enabled must be set to TRUE to use this function.

- Create a table called LOBS_TABLE in SCOTT Schema

drop table lobs_table;

create table lobs_table 
(blob_id number primary key,
 char_clob clob default empty_clob(),
 image_blob blob default empty_blob(),
 word_blob blob default empty_blob(),
 pdf_blob blob default empty_blob(),
 Movie_blob blob default empty_blob())
 tablespace users;

Note: In this form we have 2 buttons Browse and View for Each Blob item field the purpose of Browse field is to Open file dialog box to select the file which u want to store in the database and View will retrieve that file from database back to client and open it.

Steps to follow:
- Create a simple form on LOBS_TABLE
Similar to this screen
(for Word, PDF, and Movie item i am not using any special item its just default Image item)

- To Check the progress of Uploading Files Write User-defined Trigger named "CHECK_LOB_PROGRESS" at Block level code similar to this

if webutil_file_transfer.Asynchronous_Upload_Success then 
     message('File uploaded successfully');
     message('File uploaded successfully');
else
     message('Some error in uploading...');
     message('Some error in uploading...');
end if;

- Create WHEN-CREATE-RECORD Trigger at block level to generate Unique BLOB_IDs for Table

:blob_id := to_number(to_char(sysdate,'DDMMHHMISS'));

- Create 2 buttons for each Blob Item "Browse" and "View" as shown in last screen

- On Browse Photos button write this code (This will ask for File Dialog box to choose Image )

declare
    vfilename varchar2(3000);
begin
    vfilename := client_get_file_name('c:\', file_filter => ' Image files (*.jpg)|*.jpg|');
   client_image.read_image_file(vfilename,'jpg','lobs_table.image_blob');    client_image.write_image_file(vfilename,'jpg','lobs_table.image_blob',maximize_compression,ORIGINAL_DEPTH);    
end;

- On "View" button write code similar to this (To Retrieve the file which is stored in the database)
This code will copy the file from DB to Client directory C:\WINDOWS\TEMP and Open a file from there.

declare
    vboolean boolean;
    vfilename varchar2(120) := 'c:\WINDOWS\Temp\img_'||:blob_id||to_char(sysdate,'DDMMRRRR_HH24MISS')||'.jpg';
begin
  vboolean :=
  webutil_file_transfer.DB_To_Client_With_Progress
                     ( vfilename,  --filename
                       'lobs_table', ---table of Blob item
                       'image_blob',  --Blob column name
                       'blob_id = '||:blob_id, ---where clause to retrieve the record
                       'Downloading from Database', --Progress Bar title
                       'Wait to Complete'); --Progress bar subtitle
 client_host('cmd /c start '||vfilename);
end;

- Write on Browse Document button code similar to this

declare
    vfilename varchar2(3000);
    vboolean boolean;
begin
    vfilename := client_get_file_name('c:\',file_filter => 'Document files (*.doc)|*.doc|');
    
    vboolean := webutil_file_transfer.Client_To_DB_With_Progress
                     (   vfilename, 
                         'lobs_table', 
                         'word_blob',
                         'blob_id = '||:blob_id,
                         'Progress',
                         'Uploading File '||vfilename,
                         true,  ---Asynchronous uploading
                         'CHECK_LOB_PROGRESS'); ---User Call back trigger 
                         
end;

Note: With Client_To_DB_With_Progress Procedure a progress bar will appear automatically when file is uploading to or retrieving from Database.

- Write similar code for View Document button as View Image button Only change the extension of the file at the end.

- Repeat the code same as Browse and View Document for Rest of Blobs Item (PDF and Movie)

- Run the form and Create button Press Browse button first after loading of Your file a message will appear File uploaded successfully (Not for Images, Image will appear in Image item by default)

- Save the record

- Press View to View the file stored in database.

Conclusion:
Webutil is very useful library specially for interaction with Client files and directories. By using webutil we can enhance our Forms based applications. By storing files in the database we can ease the administration of important files like Centralized Repository and backup etc etc.

Whats next:
- Change the Image item types for Word, Pdf and movies to some meaningful items and In POST-QUERY also provide some info that Files are already into the database.

Tip: You can use DBMS_LOB.getlength(lob_column_name)  function to get the confirmation that Lobs are stored into the database

You can follow me on Twitter as well

Have a nice day,
Baig