1 2 3 4 Previous Next 51 Replies Latest reply: Apr 18, 2008 8:19 AM by Simon Gadd RSS

    how to save pdf in APEX 3.0

    Binepal
      is there a way to save the pdf output into APEX database in ver 3
        • 1. Re: how to save pdf in APEX 3.0
          569846
          We would be really interested in a feature like this too.

          Is there a way to catch the pdf file sent back to Apex from the report server (e.g. BI Publisher) and save it in the db before showing it to the end user???
          • 2. Re: how to save pdf in APEX 3.0
            Marc Sewtz
            This is currently not possible, at least not with the built-in functionality. The way this currently works, is that we generate the XML data and XSL-FO stylesheet, post this to the rendering engine via http (BI Publisher or Apache FOP, etc), and then get the PDF back, which is processed inside the database as a BLOB and then send to the browser. So catching it right there, and storing it e.g. in some table, would not be particularly difficult. It's just not implemented yet - I'll make a note to consider this for the next release.

            Regards,
            Marc
            • 3. Re: how to save pdf in APEX 3.0
              569846
              Hi Mark, thank you for your reply.
              When you say "inside the database", can you tell me please where exactely that BLOB is stored?
              I have tried some auditing but I have not been able to get this piece of information.
              Thanks in advance
              • 4. Re: how to save pdf in APEX 3.0
                Marc Sewtz
                It's not stored in the database, it's just passed through from the request to BI Publisher to the download process.

                Regards,
                Marc
                • 5. Re: how to save pdf in APEX 3.0
                  569846
                  Hi Marc thank you again.
                  One last question about this argument: while this BLOB passes through the database, is it possible to catch it?
                  Or we have to wait for the next Apex release for this feature to be available?
                  • 6. Re: how to save pdf in APEX 3.0
                    Marc Sewtz
                    Catching it would require change the code that's taking care of posting the XML and retrieving the PDF. If we decide to change that code, then that's not going to be available until the next release.

                    Having said that though, coding your own interface won't be particularly difficult. Our JSP that we published on OTN tells you want arguments are expected (regardless of whether you're ultimately using the JSP, Cocoon or BI Publisher). And the way we call this from the DB is via HTTP using the utl_http PL/SQL package. So if you generate your own XML data, and the post that along with your XSL-FO, you could generate PDFs that way and store them in the DB. Obviously I can't post the code here, put perhaps this gives you some ideas.

                    Regards,
                    Marc
                    • 7. Re: how to save pdf in APEX 3.0
                      Binepal
                      Marc, can you post a link to the code
                      thanks
                      • 8. Re: how to save pdf in APEX 3.0
                        Marc Sewtz
                        The APEX internal code won't make much sense out-of-context, and besides that code can't be published of course. So until I get a chance to publish a sample or paper on OTN, I hope that the info I posted in this thread gives you guys some pointers on how to start.

                        Marc
                        • 9. Re: how to save pdf in APEX 3.0
                          569846
                          Thanks for your explanation, very clear.
                          Best regards
                          • 10. Re: how to save pdf in APEX 3.0
                            135285
                            Hi,

                            based on Marc's suggestion to use UTL_HTTP to get the PDF I played around a little bit. But I didn't try to directly communicate with the report server which would require to send and XML stream which I would have to generate, I took a shortcut and thought why shouldn't APEX do the hard work :-)

                            1) Create a table with a BLOG, eg.
                            CREATE TABLE TESTPDF (PDF_REPORT BLOB);
                            2) Set the "Static ID" region property to a value.
                            3) Load the below procedure into your application schema
                            3) Call the procedure getPDF in one of your page processes.
                            CREATE OR REPLACE PROCEDURE getPDF
                              ( pStaticRegionId IN VARCHAR2
                              )
                            IS
                                vRegionId  APEX_APPLICATION_PAGE_REGIONS.REGION_ID%TYPE;
                                vReportURL VARCHAR2(255);
                                vBlobRef   BLOB;
                                vRequest   Utl_Http.req;
                                vResponse  Utl_Http.resp;
                                vData      RAW(32767);
                            BEGIN
                                ----------------------------------------------------------------------------
                                -- get internal region id of the report region
                                ----------------------------------------------------------------------------
                                SELECT REGION_ID
                                  INTO vRegionId
                                  FROM APEX_APPLICATION_PAGE_REGIONS
                                 WHERE APPLICATION_ID = Apex_Application.g_flow_id
                                   AND PAGE_ID        = Apex_Application.g_step_id
                                   AND STATIC_ID      = pStaticRegionId
                                ;
                                ----------------------------------------------------------------------------
                                -- build URL to call the report
                                ----------------------------------------------------------------------------
                                vReportURL := 'http://apex.oracle.com/pls/otn/f?p='||
                                              Apex_Application.g_flow_id      ||':'||
                                              Apex_Application.g_step_id      ||':'||
                                              Apex_Application.g_instance     ||':'||
                                              'FLOW_XMLP_OUTPUT_R'||vRegionId||'_en';
                                ----------------------------------------------------------------------------
                                -- get the blob reference
                                ----------------------------------------------------------------------------
                                INSERT INTO TESTPDF
                                  ( PDF_REPORT
                                  )
                                VALUES
                                  ( Empty_Blob()
                                  )
                                RETURNING PDF_REPORT INTO vBlobRef;
                                ----------------------------------------------------------------------------
                                -- get the pdf file from APEX by simulating a report call from the browser
                                ----------------------------------------------------------------------------
                                vRequest := Utl_Http.begin_request(vReportUrl);
                                Utl_Http.set_header(vRequest, 'User-Agent', 'Mozilla/4.0');
                                vResponse := Utl_Http.get_response(vRequest);
                                LOOP
                                    BEGIN
                                        --------------------------------------------------------------------
                                        -- read the next junk of binary data
                                        --------------------------------------------------------------------
                                        Utl_Http.read_raw(vResponse, vData);
                                        --------------------------------------------------------------------
                                        -- append it to our blob for the pdf file
                                        --------------------------------------------------------------------
                                        Dbms_Lob.writeAppend
                                          ( lob_loc => vBlobRef
                                          , amount  => Utl_Raw.length(vData)
                                          , buffer  => vData
                                          );
                                    EXCEPTION WHEN Utl_Http.END_OF_BODY THEN
                                        EXIT; -- exit loop
                                    END;
                                END LOOP;
                                Utl_Http.end_response(vResponse);
                            end getPDF;
                            Haven't really be able to test the code with a real APEX report, because apex.oracle.com doesn't allow to load a page which references utl_http and I don't have access to another APEX instance right now. But I have tested that the utl_http code and the storage into the blob works with some other URL.

                            So if anybody could try out...

                            BTW, the above code should only be used as an example. I would suggest to pass the BLOB reference as parameter to the procedure and not do the insert directly in the procedure. That way you can use it for several different applications/tables.

                            Patrick
                            ----------------------------------------------------------------------------------------------------
                            My APEX Blog: http://inside-apex.blogspot.com
                            The ApexLib Framework: http://apexlib.sourceforge.net
                            The APEX Builder Plugin: http://sourceforge.net/projects/apexplugin/
                            • 11. Re: how to save pdf in APEX 3.0
                              Binepal
                              can not compile getPDF getting error "PLS-00302: component 'G_STEP_ID' must be declared"
                              • 12. Re: how to save pdf in APEX 3.0
                                135285
                                My fault.
                                Apex_Application.g_step_id
                                has to be replaced by
                                Apex_Application.g_flow_step_id
                                Patrick
                                ----------------------------------------------------------------------------------------------------
                                My APEX Blog: http://inside-apex.blogspot.com
                                The ApexLib Framework: http://apexlib.sourceforge.net
                                The APEX Builder Plugin: http://sourceforge.net/projects/apexplugin/
                                • 13. Re: how to save pdf in APEX 3.0
                                  Binepal
                                  now i am getting error "ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1222 ORA-12545: Connect failed because target host or object does not exist"
                                  eventhough i can get the pdf genrated by putting the url being passed in procudure
                                  • 14. Re: how to save pdf in APEX 3.0
                                    135285
                                    Have you already tried if you are able to make a "callout" from your database? Eg. try the SQL statement I posted at http://inside-apex.blogspot.com/2007/02/integrating-yahoo-pipes-into-apex.html in the SQL Workshop.

                                    See also http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/net12500.htm#sthref3488 for a description of the error.

                                    What do you mean with "eventhough i can get the pdf genrated by putting the url being passed in procudure"? How do you know that it's getting generated?

                                    Patrick
                                    ----------------------------------------------------------------------------------------------------
                                    My APEX Blog: http://inside-apex.blogspot.com
                                    The ApexLib Framework: http://apexlib.sourceforge.net
                                    The APEX Builder Plugin: http://sourceforge.net/projects/apexplugin/
                                    1 2 3 4 Previous Next