9 Replies Latest reply on Jan 29, 2013 6:34 PM by MLBrown

    View pdf file stored in oracle database through oracle forms

      Forms [32 Bit] Version (Production)
      Oracle9i Enterprise Edition Release - 64bit Production
      With the Partitioning, OLAP and Oracle Data Mining options
      JServer Release - Production
      Oracle Toolkit Version (Production)
      PL/SQL Version (Production)
      Oracle Procedure Builder V10. - Production
      PL/SQL Editor (c) WinMain Software (www.winmain.com), v1.0 (Production)
      Oracle Query Builder - Production
      Oracle Virtual Graphics System Version (Production)
      Oracle Tools GUI Utilities Version (Production)
      Oracle Multimedia Version (Production)
      Oracle Tools Integration Version (Production)
      Oracle Tools Common Area Version
      Oracle CORE     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,
      FROM test_blob

      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
          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.


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

          • 2. Re: View pdf file stored in oracle database through oracle forms
            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:
               (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)
              view_file     blob;
              select doc_blob
                into view_file
                from case_documents
               where id_document = p_id_document;
              HTP.P ('CONTENT-LENGTH: ' || DBMS_LOB.GETLENGTH (view_file));
              WPG_DOCLOAD.download_file (view_file);
            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. 
              v_file          varchar2(400);
              v_success       boolean;
              ret_val         number;
              v_http_link     varchar2(400);
              -- 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');
            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
            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):

            $> 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
              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
                I have created this procedure to write the blob to file.

                CREATE OR REPLACE PROCEDURE Write_BLOB_To_File(p_id in number)
                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;


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

                -- -------------------------------------------------------------
                -- -------------------------------------------------------------
                v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc);

                IF (v_chunksize < 32767) THEN
                v_buffer_size := v_chunksize;
                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

                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);


                -- -------------------------------------------------------------
                -- -------------------------------------------------------------

                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
                  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
                    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
                      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
                        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
                          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)