This discussion is archived
1 2 3 Previous Next 35 Replies Latest reply: Jul 12, 2013 9:24 AM by Andreas Weiden RSS

How to create multiple PDF files.

tparvaiz Newbie
Currently Being Moderated
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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Hi,

    yes, that's the approach.
  • 8. Re: How to create multiple PDF files.
    tparvaiz Newbie
    Currently Being Moderated
    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.
    974888 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    anyone...?
  • 14. Re: How to create multiple PDF files.
    Andreas Weiden Guru
    Currently Being Moderated
    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

Legend

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