1 2 3 Previous Next 35 Replies Latest reply: Jul 12, 2013 11:24 AM by Andreas Weiden RSS

    How to create multiple PDF files.

    tparvaiz
      Hi,

      I have two tables, one with the customer address and another one with a note. May I know how to create a separate PDF file (for each customer) that should start with the customer name/address (from customer table) and followed by a note (from the note table)

      Thanks in advance
        • 1. Re: How to create multiple PDF files.
          Zulqarnain
          Hi

          You need to use some reporting tool such as Jasper Reports (free) or BI Publisher (very expensive) to create such reports. There are also some other options but they are more complex.

          ---
          Zulqarnain
          MaxApex Hosting
          http://www.maxapex.com
          • 2. Re: How to create multiple PDF files.
            tparvaiz
            How would you integrate Jasper (seems like it's a totally different application)... I was thinking of a solution within APEX

            Thanks
            • 3. Re: How to create multiple PDF files.
              Zulqarnain
              Hi

              See www.opal-consulting.de/tools/jasper_integration

              Apex is not very good at reporting or it is actually not meant for reporting. Therefore some of the available options are BI Publisher, Jasper, PL/PDF etc

              Zulqarnain
              MaxApex Hosting
              http://www.maxapex.com
              • 4. Re: How to create multiple PDF files.
                tparvaiz
                In the past I've used BI-publisher desktop to create template and uploaded it to Shared Components -> Report Layouts section (as an RTF)...... and created associated query and uploaded it to Shared Components -> Report Queries....... and called the report via a form with a reference PRINT_REPORT = xyz

                it worked fine for a single query... now I need to know how to call it for every record that I have in the customer table
                • 5. Re: How to create multiple PDF files.
                  Andreas Weiden
                  There is an option to use Jasper-reports-definitions in APEX from within the database. have a look here http://sourceforge.net/projects/pljrxml2pdf/ , you also try it at apex.oracle.com here http://apex.oracle.com/pls/apex/f?p=13767

                  Edited by: Andreas Weiden on 12.01.2013 17:24
                  • 6. Re: How to create multiple PDF files.
                    tparvaiz
                    Hi,

                    Had a chance to take a quick look at your suggestion... it seems like I have to do following

                    1) generate JRXML code using Jaspersoft ireport designer 5.0.0
                    2) save the code in the oracle Table (to call it later)
                    3) call the code using pl/sql

                    Can you please comment...

                    I will look into the details tomorrow.
                    • 7. Re: How to create multiple PDF files.
                      Andreas Weiden
                      Hi,

                      yes, that's the approach.
                      • 8. Re: How to create multiple PDF files.
                        tparvaiz
                        Hi,

                        I am stuck at the first step of designing report with jasper ireport designer... my test data is currently on apex.oracle.com and my real data is hosted on the intranet, there are quite a few options available to link to the table source before designing the report but I am not sure which one to use to connect to APEX.oracle.com (or to my intranet.. interestingly there is no option for ODBC connection, usually that's the one I use to upload files to oracle)...

                        can you please suggest how to link to the table...?

                        alternatively, I downloaded the data and saved it in the csv format and used that as the source to create a sample report which is shown below (is there a way to manually convert it to sql based)...? here is the report




                        <?xml version="1.0" encoding="UTF-8"?>
                        <jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report9" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="62e163c3-8f34-451a-800b-5f4285e2e194">
                             <property name="ireport.zoom" value="1.0"/>
                             <property name="ireport.x" value="0"/>
                             <property name="ireport.y" value="0"/>
                             <field name="MONTH" class="java.lang.String"/>
                             <field name="CUSTOMER" class="java.lang.String"/>
                             <field name="PRODUCT" class="java.lang.String"/>
                             <field name="Revenue" class="java.lang.String"/>
                             <background>
                                  <band splitType="Stretch"/>
                             </background>
                             <title>
                                  <band height="79" splitType="Stretch"/>
                             </title>
                             <pageHeader>
                                  <band height="35" splitType="Stretch"/>
                             </pageHeader>
                             <columnHeader>
                                  <band height="61" splitType="Stretch">
                                       <staticText>
                                            <reportElement uuid="e7032731-30c5-4d15-b6e0-bccaeb8de3b0" x="0" y="0" width="138" height="20"/>
                                            <textElement/>
                                            <text><![CDATA[MONTH]]></text>
                                       </staticText>
                                       <staticText>
                                            <reportElement uuid="0a3a1aef-e967-42c4-81bd-1fc0688afffa" x="138" y="0" width="138" height="20"/>
                                            <textElement/>
                                            <text><![CDATA[CUSTOMER]]></text>
                                       </staticText>
                                       <staticText>
                                            <reportElement uuid="35720a70-999c-4b17-966d-7a6ff104ccd2" x="276" y="0" width="138" height="20"/>
                                            <textElement/>
                                            <text><![CDATA[PRODUCT]]></text>
                                       </staticText>
                                       <staticText>
                                            <reportElement uuid="bbec6818-cdb0-4745-927c-6f3e40209fb2" x="414" y="0" width="138" height="20"/>
                                            <textElement/>
                                            <text><![CDATA[Revenue]]></text>
                                       </staticText>
                                  </band>
                             </columnHeader>
                             <detail>
                                  <band height="125" splitType="Stretch">
                                       <textField>
                                            <reportElement uuid="a6554549-7721-4a4f-94f0-3169139bfdb6" x="0" y="0" width="138" height="20"/>
                                            <textElement/>
                                            <textFieldExpression><![CDATA[$F{MONTH}]]></textFieldExpression>
                                       </textField>
                                       <textField>
                                            <reportElement uuid="c2943016-5a5f-42e8-8612-5b8dafc23eea" x="138" y="0" width="138" height="20"/>
                                            <textElement/>
                                            <textFieldExpression><![CDATA[$F{CUSTOMER}]]></textFieldExpression>
                                       </textField>
                                       <textField>
                                            <reportElement uuid="3d97fdd0-5923-469a-862f-be485b6416ac" x="276" y="0" width="138" height="20"/>
                                            <textElement/>
                                            <textFieldExpression><![CDATA[$F{PRODUCT}]]></textFieldExpression>
                                       </textField>
                                       <textField>
                                            <reportElement uuid="724e9536-667b-4db1-96b8-81e2f5554ea2" x="414" y="0" width="138" height="20"/>
                                            <textElement/>
                                            <textFieldExpression><![CDATA[$F{Revenue}]]></textFieldExpression>
                                       </textField>
                                  </band>
                             </detail>
                             <columnFooter>
                                  <band height="45" splitType="Stretch"/>
                             </columnFooter>
                             <pageFooter>
                                  <band height="54" splitType="Stretch"/>
                             </pageFooter>
                             <summary>
                                  <band height="42" splitType="Stretch"/>
                             </summary>
                        </jasperReport>
                        • 9. Re: How to create multiple PDF files.
                          user1164246
                          u may use oracle report call another report using srw.run_report.
                          In report 1
                          create listing by the customer name
                          create format trigger at the customer_name column and use srw.run_report to call report 2 to generate the pdf detail.
                          • 10. Re: How to create multiple PDF files.
                            tparvaiz
                            user1164246 , that's fantastic... I will look into it once I'll resolve my first roadblock (generating JXML code)

                            Thanks again for your assistance. all of you folks are superstars
                            • 11. Re: How to create multiple PDF files.
                              Andreas Weiden
                              You would use a JDBC-connection to the database and the query has to be defined in SQL. Inside the database the query is then parsed and executed using DBMS_SQL.
                              • 12. Re: How to create multiple PDF files.
                                tparvaiz
                                So far I am able to create a report using Jasper iReport Designer... posted associated JRXML code on APEX Oracle table and called it through a PL/SQL. but unfortunately got an error message

                                Here is the error message

                                fail
                                ORA-01403: no data found
                                Technical Info (only visible for developers)
                                is_internal_error: false
                                ora_sqlcode: 100
                                ora_sqlerrm: ORA-01403: no data found
                                component.type: APEX_APPLICATION_PAGE_PROCESS
                                component.id: 9473327712428202341
                                component.name: Generate PDF Reports
                                error_backtrace:
                                ORA-06512: at "BRR.PK_JRXML2PDF_LOADER", line 7390
                                ORA-06512: at "BRR.PK_JRXML2PDF_REPGEN", line 204
                                ORA-06512: at "BRR.PK_JRXML2PDF_REPGEN", line 8037
                                ORA-06512: at "BRR.PK_JRXML2PDF_REPGEN", line 8058
                                ORA-06512: at line 19
                                ORA-06512: at "SYS.DBMS_SYS_SQL", line 1926
                                ORA-06512: at "SYS.WWV_DBMS_SQL", line 973
                                ORA-06512: at "SYS.WWV_DBMS_SQL", line 999
                                ORA-06512: at "APEX_040200.WWV_FLOW_DYNAMIC_EXEC", line 830
                                ORA-06512: at "APEX_040200.WWV_FLOW_PROCESS", line 138

                                Seems like there is something wrong with my PL/SQL script... here is what I've usedDECLARE
                                CURSOR crReport IS
                                SELECT JRDXML_CODE
                                FROM T_JRDXML_CODE;

                                vcName VARCHAR2(4200);
                                rParam PK_JRXML2PDF_REPGEN.tParameter;
                                lParams PK_JRXML2PDF_REPGEN.tParamList;
                                bl BLOB;
                                BEGIN
                                OPEN crReport;
                                FETCH crReport INTO vcName;
                                CLOSE crReport;

                                -- Add parameters to report, if any
                                rParam.vcName:='NAME';


                                bl :=PK_JRXML2PDF_REPGEN.FK_RUN(i_vcName =>vcName);
                                COMMIT;
                                PK_JRXML2PDF_REPGEN.PR_SHOW_REPORT(bl);
                                APEX_APPLICATION.STOP_APEX_ENGINE;
                                END;


                                I've saved a demo copy on APEX.ORACLE.COM as follows for your review and consideration


                                Workspace: TPARVAIZ1
                                Username: REDGREENRED@HOTMAIL.COM
                                Password: mucew0

                                Application: 24741 (Jasper iReport Developer)
                                ID/PW: TEST/TEST

                                Thanks again for your assistance.
                                • 13. Re: How to create multiple PDF files.
                                  tparvaiz
                                  anyone...?
                                  • 14. Re: How to create multiple PDF files.
                                    Andreas Weiden
                                    Hi,

                                    you misunderstood the concept a little.

                                    You do not have to create your own table for the report-definition, there is already on named JRXML_REPORT_DEFINITIONS. There you insert the jrxml-code along with an identifying name.

                                    When calling the report, you just give the name, not the jrxml itself.

                                    Also, you can use the installed application PL-jrxml2pdf to administer the table and test the reports.

                                    I've inserted your report into the table JRXML_REPORT_DEFINITIONS with the name 'YourReport' and adjusted the code in your homepage:
                                    DECLARE
                                      bl BLOB;
                                    BEGIN
                                      --
                                      --  -- Add parameters to report, if any
                                      --  rParam.vcName:='NAME';
                                      --
                                    
                                      bl :=PK_JRXML2PDF_REPGEN.FK_RUN(i_vcName =>'YourReport');
                                      COMMIT;
                                      PK_JRXML2PDF_REPGEN.PR_SHOW_REPORT(bl);
                                      APEX_APPLICATION.STOP_APEX_ENGINE;
                                    END;
                                    The report run fine now.
                                    1 2 3 Previous Next