Sunday, February 21, 2010

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

31 comments:

  1. Hi Baig

    Greate content. You information is very usefull and very clear.

    Thanks for all the efforts.

    Diogo Fernandes

    ReplyDelete
  2. i am glad that you found my blog useful

    ReplyDelete
  3. Hi I am getting the following error,
    FRM-40735:When-Button-Pressed trigger raised unhandled exception ORA-04067

    ReplyDelete
  4. Hi make sure you have properly configures webutil with database package.

    check here for more info

    http://baigsorcl.blogspot.com/2010/01/open-file-dailog-box-example-using.html

    ReplyDelete
  5. Hi,

    I tried your 'form' and it the "Browse Photo" work perfectly. however the "View Photo" got error. "Window can not find error" appear because there is no image uploaded. Please help.
    Below are the codes:

    openfile := webutil_file_transfer.Client_To_DB_With_Progress
    (
    filename,
    'IR_IMAGE',
    'IMAGE_BLOB',
    'BLOB_ID ='||'1101102032',
    'Progress',
    'Uploading File '||filename,
    TRUE,
    'CHECK_LOB_PROGRESS'
    );

    ReplyDelete
  6. In view i am copying the file to temporary folder and then opening in some application. Make sure you have enough permission on temporary folder which is i guess c:\temp You can change yourself to new location.

    ReplyDelete
  7. Hi Bro Baig..

    can you please send me your working example as it is not found or removed from 4share.. Can you please send it to my account.."wazirzadakhan@gmail.com"..

    Waiting for your reply..

    Regards

    Wazir..

    ReplyDelete
  8. Hi Wazir,

    I have updated the file location path.

    ReplyDelete
  9. hI Baig..

    I got your file "working_with_lobs"..

    I configure webutil successfully and it works very fine and also successfully run your second example "working_with_lob" for uploading files to db..

    i have stored images successfully but facing problem while storing pdf, doc or mov files But i have a little problem.. the problem is if i first upload a image file then it also upload pdf file but if i directly try to store pdf then it not working.. i have checked all possible options but i dnt know why this is happening???

    Can you advice some thing..

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Hi Baig,

    I like the concept of your form. I was wondering how difficult would it be to make a similar form like yours into one for 11g (forms and db)? I am trying to work on a proof of concept for a standalone attachments form and am very new to 11g.

    Any help/suggestions you may have would be greatly appreciated.

    ReplyDelete
  12. Hi Nik,

    Forms 11g is similar to Forms 10g except the deployment server is not weblogic. I hope you can run my sample form in forms11g without any issues.

    ZB

    ReplyDelete
  13. Hi Baig,

    I noticed that when I saved the same image using both client_image.read_image_file() and webutil_file_transfer.Client_to_DB_with_progress() that the file sizes saved is significantly different. If I use the latter function call to try to view the image, it is not viewable.

    Any idea why that is?
    Thanks
    -Nik

    ReplyDelete
  14. Hi Nik,

    Read image file will read image from OS and client_to_db will store in database in compress form as i passed a maximum_compression parameter.

    if you want to read file from DB try DB_T0_CLIENT as i did in the post

    I am not up-to date with Oracle Forms unfortunately can't be much helpful.

    ZB

    ReplyDelete
  15. How I can do the same thing in oracle JDeveloper 11g

    ReplyDelete
  16. Hi Arshad,

    Check this link http://www.baigzeeshan.com/2010/09/store-images-in-blob-in-oracle-adf.html

    ReplyDelete
  17. Zeeshan,

    Many thanks this was a great post. Wondering if you could point me in a direction as I'm having trouble with the call to:

    webutil_file_transfer.Client_To_DB_With_Progress

    it always returns false. I get the file dialog
    select my file, but then nothing. See below for code snippet. Thanks in advance for any suggestions!

    John G



    declare
    vfilename varchar2(3000);
    vboolean boolean;
    begin
    vfilename := client_get_file_name('c:\', file_filter => 'All files (*.*)|*.*|');
    MESSAGE(VFILENAME);
    MESSAGE(VFILENAME);
    vboolean := webutil_file_transfer.Client_To_DB_With_Progress(vfilename,
    'INVOICE_AUTOMATION_ATTACHMENTS',
    'ATTACHMENT_FILE_BLOB',
    'INVOICE_AUTOMTN_ATTACHMENT_KEY = 115',
    'Upload To Database In Progress',
    'Please Wait....',
    FALSE,
    NULL);
    IF VBOOLEAN THEN
    MESSAGE('TRUE');
    MESSAGE('TRUE');
    ELSE
    MESSAGE(TO_CHAR(:INVOICE_AUTOMATION_ATTACHMENTS.INVOICE_AUTOMTN_ATTACHMENT_KEY));
    MESSAGE(TO_CHAR(:INVOICE_AUTOMATION_ATTACHMENTS.INVOICE_AUTOMTN_ATTACHMENT_KEY));
    END IF;
    end;

    ReplyDelete
  18. I hope this is not a dumb question, but how would you suggest checking file size before loading an image? I would like to handle the check on the forms side of things if possible. Also, great post, I was dreading this part of my project and you made it simple. Thanks.

    ReplyDelete
  19. I used your scripts and followed same exercise but once i press the button it populate window and select any jpg file but it does not show any thing and silent without any error. I do not know what problem.

    ReplyDelete
    Replies
    1. Try to catch exception if it is missing.

      Zeeshan

      Delete
  20. Hi Zeeshan,
    I am very new to Oracle and I have a requirement to read a word file using pl/sql code. Could you please me here?

    Thanks in advance!

    Regards,
    Abhi

    ReplyDelete
  21. HI ZEESHAN...
    I used Similer method as you mention above,after doing all step when i am rouning my form.an error is howing on when i press button to upload image;

    "FRM-40734 INTERNEL ERROR PLSQL ERROR OCCUE......PLEASE HELP ME

    ReplyDelete
  22. sir,
    my application server is on linux. i do the following step to run the form for word document but it do nothing i think hang or something....
    1. open the form builder and attache webutil.pll
    2. copy the block of webutil
    3. copy the canvas for the webutil block these are copy from anthor form
    4. put a button and past the code like

    declare
    arg_list client_ole2.list_type;
    document client_ole2.obj_type;
    documents client_ole2.obj_type;
    application client_ole2.obj_type;
    BEGIN
    clear_record;
    clear_message;
    -- application := client_OLE2.CREATE_OBJ('WORD.APPLICATION');

    arg_list := client_ole2.create_arglist;
    documents := client_ole2.invoke_obj (application, 'documents');
    client_ole2.add_arg (arg_list, 'C:\maha.DOC');
    document := client_ole2.invoke_obj (documents, 'Open', arg_list);
    client_ole2.destroy_arglist (arg_list);
    client_ole2.RELEASE_OBJ (documents);
    exception
    when others then
    message(SQLERRM);
    message(SQLERRM);

    end;

    5. compile the form on linux.
    6. run the form on client which is win 7
    but the form do nothing
    please mention which step is missued i think specially in webutil because the in form builder when other ole is used which not for webutil method the file opend and works but in webutil is not why???

    Report message to a moderator

    ReplyDelete
    Replies
    1. Hi,
      Your code will run on the server side so make sure you write for it.

      There is no C:\ on Linux you have to use linux based addresses like /opt/ora/files etc etc
      client_ole2.add_arg (arg_list, 'C:\maha.DOC');

      Hope it helps,
      Zeeshan

      Delete
  23. for forms 6i how can i do this same example
    plz help me

    ReplyDelete
    Replies
    1. Hi,

      Its been a long time I used 6i or FORMS infact. Use READ_IMAGE_FILE and WRITE_IMAGE_FILE procedures to read and write images or other blobs from database.

      Regards,
      Zeeshan

      Delete
  24. please help for browse file without webutil

    ReplyDelete
    Replies
    1. You have to use webutil for web based Forms.

      Zeeshan

      Delete
  25. Hi Zeeshan,

    Great blog. Thanks for sharing the useful info.

    However, I am facing a small issue while opening word documents from Oralce forms 10g.

    I am using following commands:
    > client_host('cmd /c ' || :file_path);
    > proc_id := WEBUTIL_HOST.Blocking( 'cmd /c start /WAIT WINWORD /W "' || file_path || '"');

    These are blocking commands which will lock the form until the word application is closed. However, when the word application is closed, the calling form remains locked for sometime (ranging from 4 sec to 15 sec) after which the control comes back. I have not idea why its happening. can you suggest what should I check.

    Clients are using Windows 8 machines having JRE 1.6.0.45. (Clients with Win XP having JRE 1.6.24 are working fine).

    Thanks and Kind Regards
    Munib

    ReplyDelete
    Replies
    1. Hi,

      Sorry I have been out of touch with Forms for almost a decade. The answer is in your question as well. I don't think Forms10g supports Windows 8 you need to contact Oracle support team.

      Regards,
      Zeeshan

      Delete