This discussion is archived
9 Replies Latest reply: Jan 29, 2013 10:34 AM by MLBrown RSS

View pdf file stored in oracle database through oracle forms

user_anumoses Newbie
Currently Being Moderated
Forms [32 Bit] Version 10.1.2.0.2 (Production)
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
Oracle Toolkit Version 10.1.2.0.2 (Production)
PL/SQL Version 10.1.0.4.2 (Production)
Oracle Procedure Builder V10.1.2.0.2 - Production
PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
Oracle Query Builder 10.1.2.0.2 - Production
Oracle Virtual Graphics System Version 10.1.2.0.2 (Production)
Oracle Tools GUI Utilities Version 10.1.2.0.2 (Production)
Oracle Multimedia Version 10.1.2.0.2 (Production)
Oracle Tools Integration Version 10.1.2.0.2 (Production)
Oracle Tools Common Area Version 10.1.2.0.2
Oracle CORE     10.1.0.4.0     Production

___________________

I have created external directory and am able to load pdf files in oracle database table called test_blob.

CREATE TABLE test_blob (
id NUMBER(15)
, file_name VARCHAR2(1000)
, image BLOB
, timestamp DATE
)





I have 2 pdf files in the table. I want to view this pdf from forms when the user clicks on the button. On when-button-pressed trigger I want to show pdf on the screen. Any help is appreciated. Not on the designer. I want to run form application.



SELECT id, file_name,
DBMS_LOB.GETLENGTH(image) Length,
timestamp
FROM test_blob

ID|FILE_NAME|LENGTH|TIMESTAMP
1001|2011 HeartlandEmployeeReferralCard.pdf|353718|1/28/2013 11:44:41 AM
1002|2011 HeartlandEmployeeReferralCard.pdf|353718|1/28/2013 11:51:07 AM

Edited by: user_anumoses on Jan 28, 2013 11:45 AM
  • 1. Re: View pdf file stored in oracle database through oracle forms
    Michael Ferrante (Oracle) Guru Moderator
    Currently Being Moderated
    Forms has no native way to extract the pdf from the blob in a way that it can be displayed. You can however, use WEBUTIL to download the file to the client machine then display it or you can do something like what is described in the following. This will give you a way to retrieve the documents using a URL.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:232814159006

    Once you are able to retrieve the documents using a URL, you can then easily call them from Forms using WEB.SHOW_DOCUMENT

    Reference:
    http://www.google.com/search?q=oracle+read+blob+pdf+url
  • 2. Re: View pdf file stored in oracle database through oracle forms
    MLBrown Journeyer
    Currently Being Moderated
    We were able to do the same thing with Oracle Application Server and Oracle WebLogic Server. I cannot remember how different the processes were, but it seems like they were very similar. I am going to give you the instructions on how we implemented a "Read PDF" procedure on the WebLogic Server. If you are still on the Application Server you may have to do some Google searches, but it all boils down to the mod_plsql DAD Configuration file.

    Our PDF was located in a table with the following structure:
    CASE_DOCUMENTS
       (id_document                    NUMBER NOT NULL,
        doc_blob                       BLOB,
        note                           VARCHAR2(240),
        created_by                     VARCHAR2(20) NOT NULL,
        created_dt                     DATE NOT NULL,
        case_id                        NUMBER NOT NULL,
        filename                       VARCHAR2(100) NOT NULL)
    Based on that table structure we created a procedure named READ_PDF which you will reference below in the dads.conf file below:
    CREATE or REPLACE procedure read_pdf (p_id_document IN number)
    is
      view_file     blob;
    BEGIN
      select doc_blob
        into view_file
        from case_documents
       where id_document = p_id_document;
    
      OWA_UTIL.MIME_HEADER ('APPLICATION/PDF', FALSE);
      HTP.P ('CONTENT-LENGTH: ' || DBMS_LOB.GETLENGTH (view_file));
      OWA_UTIL.http_header_close;
      WPG_DOCLOAD.download_file (view_file);
    END;
    /
    
    GRANT EXECUTE ON read_pdf TO financial_user_role  -- Name of role to execute
    /
    Basically, you are passing in one parameter and that is the primary key for your table. You are selecting the pdf stored in a BLOB for that primary key. The commands below that allow the pdf to open up so you can view it – we got this off some search we did a few years ago.

    Now, you need to add logic to your Oracle Form that will call the procedure above, but the URL is based on the dads.conf file that we will set up below… Anyway, we created a button on the form module with a label of "View". In the WHEN-BUTTON-PRESSED trigger the logic looks like this:
    --
    -- The View logic uses the DAD (Database Access Descriptors) method to view a .pdf file from the form.
    -- The DAD was created on WebLogic Server  with the name findadgen.  This allows an http request be made
    -- to the database. 
    --
    declare
      v_file          varchar2(400);
      v_success       boolean;
      ret_val         number;
      v_http_link     varchar2(400);
    begin
      --
      -- The format of the link is as follows: hostname:port/pls/DAD_name/procedure_name
      --
      --
      v_http_link := 'http://finas03:8888/pls/findadgen/read_pdf?p_id_document=' || :case_documents.id_document;
      web.show_document(v_http_link, '_BLANK');
    end;
    The name of our WebLogic Server is "finas03" so that is what is listed in the URL. The "findadgen" is the name of the <Location> in the dads.conf file below, the "read_pdf" is the name of the procedure we created above, the "p_id_document=" is the IN parameter listed in the READ_PDF procedure created above, and the ":case_documents.id_document" is the reference to the primary key in our Oracle Form.

    For WebLogic, you can either go through Enterprise Manager (directions below) or update the dads.conf file on the filesystem directly (if you update the dads.conf file directly then skip to step 4 and ignore step 5):

    1.     Enterprise Manager -> Web Tier -> ohs1
    2.     Oracle HTTP Server (pull-down) – Administration – Advance Configuration
    3.     Select File – dads.conf
    4.     Add something similar:
    # ============================================================================  
    #                     mod_plsql DAD Configuration File                          
    # ============================================================================  
    # 1. Please refer to dads.README for a description of this file                 
    # ============================================================================   
    # Note: This file should typically be included in your plsql.conf file with  
    # the "include" directive.  
    # Hint: You can look at some sample DADs in the dads.README file  
    # ============================================================================
    <Location /pls/findadgen>
        SetHandler pls_handler
        Order allow,deny
        Allow from All
        AllowOverride None
        PlsqlDatabaseUsername financial
        PlsqlDatabasePassword sdo_3#d1
        PlsqlDatabaseConnectString ffindbTNSFormat
        PlsqlNLSLanguage AMERICAN_AMERICA.WE8ISO8859P1
        PlsqlAuthenticationMode Basic
        PlsqlDefaultPage read_pdf
    </Location>
    You are adding the <Location> section to your dads.conf file. The "finddadgen" is the name that you will reference in a change you fill make to your Oracle Form. The "financial" is the Schema, the "sdo_3#d1" is the password for that Schema, the "ffindb" is the database that the stored procedure is located on, and the "read_pdf" is a stored procedure you will have to create in order to read the pdf.

    5.     Press the "Apply" Button
    6.     Obfuscate the DAD password by running the dadTool.pl script located in $ORACLE_HOME/bin (This was done on Unix on our server with the following commands):

    $> LD_LIBRARY_PATH=$ORACLE_HOME/lib;export LD_LIBRARY_PATH
    $> cd $ORACLE_HOME/bin
    $> perl dadTool.pl -f /u01/app/oracle/middleware/asinst_1/config/OHS/ohs1/mod_plsql/dads.conf

    7.     Restart the Oracle HTTP Server using Fusion Middleware Control:

    Enterprise Manager -> Web Tier -> ohs1
    Oracle HTTP Server – Control – Shutdown
    Oracle HTTP Server – Control – Start Up

    If you followed the instructions above, you should have created a stored procedure, added logic to your Oracle form to reference that stored procedure, and created an entry in the dads.conf file. Once you move the form onto the server and you restart the HTTP Service, you should be able to view a pdf that is stored in a table directly from your Oracle Form.
  • 3. Re: View pdf file stored in oracle database through oracle forms
    user_anumoses Newbie
    Currently Being Moderated
    What if I don't have web logic? Will this code not work?
  • 4. Re: View pdf file stored in oracle database through oracle forms
    user_anumoses Newbie
    Currently Being Moderated
    I have created this procedure to write the blob to file.


    CREATE OR REPLACE PROCEDURE Write_BLOB_To_File(p_id in number)
    AS
    v_lob_loc BLOB;
    v_buffer RAW(32767);
    v_buffer_size BINARY_INTEGER;
    v_amount BINARY_INTEGER;
    v_offset NUMBER(38) := 1;
    v_chunksize INTEGER;
    v_out_file UTL_FILE.FILE_TYPE;

    BEGIN

    -- -------------------------------------------------------------
    -- | SELECT THE LOB LOCATOR |
    -- -------------------------------------------------------------
    SELECT image
    INTO v_lob_loc
    FROM test_blob
    WHERE id = p_id;

    -- -------------------------------------------------------------
    -- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN |
    -- -------------------------------------------------------------
    v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc);

    IF (v_chunksize < 32767) THEN
    v_buffer_size := v_chunksize;
    ELSE
    v_buffer_size := 32767;
    END IF;

    v_amount := v_buffer_size;

    -- -------------------------------------------------------------
    -- | OPENING THE LOB IS OPTIONAL |
    -- -------------------------------------------------------------
         
         --DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);

    -- -------------------------------------------------------------
    -- | WRITE CONTENTS OF THE LOB TO A FILE |
    -- -------------------------------------------------------------
    v_out_file := UTL_FILE.FOPEN(
    location => 'CONTRACTS',
    filename => '2011 HeartlandEmployeeReferralCard.pdf',
    open_mode => 'w',
    max_linesize => 32767);

    WHILE v_amount >= v_buffer_size
    LOOP

    DBMS_LOB.READ(
    lob_loc => v_lob_loc,
    amount => v_amount,
    offset => v_offset,
    buffer => v_buffer);

    v_offset := v_offset + v_amount;

    UTL_FILE.PUT_RAW (
    file => v_out_file,
    buffer => v_buffer,
    autoflush => true);

    UTL_FILE.FFLUSH(file => v_out_file);


    END LOOP;

    UTL_FILE.FFLUSH(file => v_out_file);

    UTL_FILE.FCLOSE(v_out_file);

    -- -------------------------------------------------------------
    -- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT |
    -- -------------------------------------------------------------
    --DBMS_LOB.CLOSE(v_lob_loc);
    End;

    Now I need help. From here is there a way to arrive to an url and then use web.show_document?
  • 5. Re: View pdf file stored in oracle database through oracle forms
    MLBrown Journeyer
    Currently Being Moderated
    It will work if you are using the Application Servers, but not if you are still client-server. When we originally implemented this, we were on the Application Servers (9i maybe) then we moved to WebLogic which is what these instructions are for, but it is the same steps. You would just have to locate the dads.conf file and manually modify it instead of going through Enterprise Manager.
  • 6. Re: View pdf file stored in oracle database through oracle forms
    user_anumoses Newbie
    Currently Being Moderated
    v_http_link := 'http://finas03:8888/pls/findadgen/read_pdf?p_id_document=' || :case_documents.id_document;
    web.show_document(v_http_link, '_BLANK');

    This is where I am stuck. All the steps before this work.
  • 7. Re: View pdf file stored in oracle database through oracle forms
    MLBrown Journeyer
    Currently Being Moderated
    v_http_link := 'http://finas03:8888/pls/findadgen/read_pdf?p_id_document=' || :case_documents.id_document;
    web.show_document(v_http_link, '_BLANK');
    
    This is where I am stuck. All the steps before this work. 
    Where are you stuck?

    The http://finas03:8888 is whatever server you use right now to deploy your forms. Before Weblogic, ours was something like http://finserv:7777. You would have to change this to be the name of your server and ports that are configured for your Application Server.

    The "pls/findadgen" comes from how you set up your record in the dads.conf file.

    "read_pdf" is the name of your stored procedure.

    "p_id_document" is the IN parameter in your stored procedure (read_pdf in the example I gave you).

    ":CASE_DOCUMENTS.ID_DOCUMENT" is the block and item name in your Oracle Form that contains the actual primary key for your PDF. This is passed to the parameter above.
  • 8. Re: View pdf file stored in oracle database through oracle forms
    user_anumoses Newbie
    Currently Being Moderated
    The "pls/findadgen" comes from how you set up your record in the dads.conf file.

    Can we do this from enterprise manager?
  • 9. Re: View pdf file stored in oracle database through oracle forms
    MLBrown Journeyer
    Currently Being Moderated
    I had to go back and look at some documentation on our Application Server. If these steps don't work for you, you might have to click on "Help" in your Enterprise Manager and search for "DAD" or "Database Access Descriptor".

    According to those steps:
    1. Log onto Oracle Enterprise Manager (http://finserv1:1810) -- this would be the name of you server and port
    2. Click on FIN_Midtier_1.finserv1 -- this would be the name of your Application Server
    3. Click on HTTP_Server
    4. Click on Administration
    5. Click on PL/SQL Properties (link at top: Application Server: FIN_Midtier_1.finserv1 > HTTP_Server > mod_plsql Services)
    6. Scroll down to the DAD Section
    7. Click the Create button (right-hand side of screen)
    8. Select DAD Type of General and Click Next (Step 1 of 2)
    9. Start filling in the connectivity information for the DAD
    10. Precede the DAD name or Location with a /pls in this example: /pls/findadgen
    11. Username: Enter the username where the stored procedure (READ_PDF) is located
    12. Password: Enter the password for the username you just entered.
    13. Connect String is the database you want the DAD to connect to (where the READ_PDF is located)
    14. The connect string format is: TNSFormat because the connect string is resolved through tnsnames.ora
    15. Get the NLS Language value by running the following query against the schema/database this DAD is being setup for:
    select value, parameter
      from nls_database_parameters
     where parameter in ('NLS_LANGUAGE', 'NLS_TERRITORY','NLS_CHARACTERSET');
    
    VALUE                                      PARAMETER
    ---------------------------------------- ------------------------------
    AMERICAN                                 NLS_LANGUAGE
    AMERICA                                  NLS_TERRITORY
    WE8ISO8859P1                             NLS_CHARACTERSET
    Enter the value in the format of: <NLS_LANGUAGE>_<NLS_TERRITORY>.< NLS_CHARACTERSET>
    In our example we have: AMERICAN_AMERICA.WE8ISO8859P1

    16. Default Page: Enter the procedure name that will be invoked when one is not specified as part of the URL. In our example, we use the procedure name: read_pdf
    17. Authentication Mode: Took the default of Basic
    18. Then Click Next (Step 2 of 4) in bottom right corner
    19. Click Next (Step 3 of 4) Again
    20. Click OK (Step 4 or 4)
    21. Your done. Remember the DAD or any changes made to the DAD will not take effect until the Server is restarted. Make appropriate arrangements to get that done.

    We then have a picture under the DADs sections that shows a name or Location of /pls/findadgen, the username, connect string, and status (green arrow - up)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points