{"id":4525,"date":"2019-10-16T22:33:56","date_gmt":"2019-10-16T20:33:56","guid":{"rendered":"http:\/\/gpmfactory.com\/?p=4525"},"modified":"2019-10-17T18:27:07","modified_gmt":"2019-10-17T16:27:07","slug":"using-bi-publisher-rtf-templates-with-oracle-apex","status":"publish","type":"post","link":"https:\/\/gpmfactory.com\/index.php\/2019\/10\/16\/using-bi-publisher-rtf-templates-with-oracle-apex\/","title":{"rendered":"Using BI Publisher RTF templates with Oracle APEX"},"content":{"rendered":"\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\"><p>Leverage the Oracle BI Publisher templating power and produce high fidelity reports<\/p><\/blockquote>\n\n\n\n\n\n<h2 class=\"wp-block-heading\">Abstract<\/h2>\n\n\n\n<p>Oracle APEX is a tool which is well suited for rapid applications developpement (RAD). It&rsquo;s possible to produce a tabular report based one a query in a very fast way. This is a standard feature. But when the goal is to build high precision reports \u00ab\u00a0pixel perfect\u00a0\u00bb, like an invoice, for instance, we must switch to an other approach. The good news is that Oracle APEX gives ability  to use XSLT-FO templates instead the standard ones. Perfect  ! But how authoring these XSLT-FO templates, while keeping the APEX low code philosophy ? The following in this post explains a possible solution. <\/p>\n\n\n\n<p>The approach is to use <a href=\"https:\/\/www.oracle.com\/middleware\/technologies\/bi-publisher.html\">Oracle BI PUblisher<\/a> Desktop product (BIP). It&rsquo;s a plugin for Microsft Word which \u00ab\u00a0transforms\u00a0\u00bb MS Word in a template authoring tool without burden of with XSLT and XPATH syntax. ( One other alternative is to use <a href=\"https:\/\/poi.apache.org\/\">Apache POI project<\/a> then adding XPATH expressions in the XSL-FO template) <br>Important notice:  It will be possible to use a functionnal subset of the BIP product but not all features. Therefore, the following informations must be considered as an experimental work only. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Materials &amp; Documentation<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Prerequisites<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">License Prerequisites<\/h4>\n\n\n\n<ul class=\"wp-block-list\"><li>A commercial license for using BI Publisher (BIP)<\/li><li>A open source license for the SAXON xsl parser<\/li><\/ul>\n\n\n\n<p>We&rsquo;ll not rely on BIP FOP engine and we&rsquo;ll just use the BIP Desktop plugin. The license for BIP can be purchased on a <em>Named User<\/em> metric. That means that we can manage to buy at a <em>low price<\/em> but with a minimum of 10 licenses per processor.<\/p>\n\n\n\n<p>SAXON is sold through several editions. The home edition, <a href=\"http:\/\/saxon.sourceforge.net\/#F9.9HE\">SAXON-HE<\/a> is an open source product under Mozilla public license V2. It can be enough for testing and regular use as well. <\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Download &amp; Documentation <\/h4>\n\n\n\n<ul class=\"wp-block-list\"><li>Download <a href=\"https:\/\/www.oracle.com\/middleware\/technologies\/bi-publisher\/downloads.html\">BIP desktop<\/a><\/li><li>Download <a href=\"http:\/\/saxon.sourceforge.net\/#F9.9HE\">SAXON-HE<\/a> <\/li><li>Conversion tool <a href=\"https:\/\/github.com\/patrickmonaco\/BIP_TO_FOP\">BIP_TO_FOP<\/a><\/li><\/ul>\n\n\n\n<p>Documentation <\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><a href=\"https:\/\/docs.oracle.com\/middleware\/12213\/bip\/BIPRD\/GUID-43369E75-4A64-4C70-956D-AB0DB496CA7A.htm#BIPRD2621\">BIP Desktop 12.2.1.3<\/a><\/li><li><a href=\"https:\/\/www.oracle.com\/webfolder\/technetwork\/tutorials\/obe\/fmw\/bi\/bip\/tb4word\/tbwordbip.htm\">RTF templating tutorial<\/a><\/li><\/ul>\n\n\n\n<p>About XPATH and XSLT Syntax, BIP Desktop samples are a good source of inspiration: <br>\u00ab\u00a0C:\\Program Files\\Oracle\\BI Publisher\\BI Publisher Desktop\\Template Builder for Word\\samples\\RTF templates\u00a0\u00bb<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Technical prerequisites<\/h4>\n\n\n\n<p>This post assumes a basic knowledge of RTF template design with BIP.<br>Once the RTF template is setup, we use the embeded function for exporting the template as an XSL-FO stylesheet.<\/p>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"alignright is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-17.png\" alt=\"\" class=\"wp-image-4640\" width=\"283\" height=\"106\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-17.png 339w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-17-300x112.png 300w\" sizes=\"auto, (max-width: 283px) 100vw, 283px\" \/><figcaption>Export option inside BIP Desktop plugin<\/figcaption><\/figure><\/div>\n\n\n\n<p>From BIP plugin, an export menu option gives ability to export the template as a XSL-FO stylesheet. This  exported stylesheet contains additional xsl function which are specific to BIP and can&rsquo;t be used directly in Oracle APEX. In order to use a regular FO Processor (the one imbedded in APEX), we have to either remove or convert it, when possible.<br>For that purpose, we use a simple plsql procedure which will do a kind of cleanup in the template. It&rsquo;is a fork of a Pavel Glebov&rsquo;s project <a href=\"https:\/\/github.com\/patrickmonaco\/BIP_TO_FOP\">https:\/\/github.com\/patrickmonaco\/BIP_TO_FOP<\/a>.<br>Some additional details have been added. <br>The XSL-FO beeing a regular XML document,it would have been ideally more judicious to use a XSLT parsing method, but this discussion is out of the scope of this post.  <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Implementation<\/h2>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Reminders about the FOP engine<\/h3>\n\n\n\n<p>  An Apache FOP engine 2.3 is embedded in the ORDS java application. This engine relies on a XALAN (XSLTC) transformerFactory. This parser is compliant whit XSLT V1 but not XSLT v2. And this is a little bit ennoying for our purpose because some group functions generated by BIP should have to be rewritten for working in XSLT v1. Because we don&rsquo;t want to developp a too complex convert tool, I prefered susbitute for XALAN (XSLTC) an other XSLT parser (SAXON) in order to minimize conversion effort. <\/p>\n\n\n\n<p>This parser, SAXON, is XSLT v2 compliant . Keep in mind that license fees can be required in some cases.<\/p>\n\n\n\n<p>So, once we setup the new transformerFactory (new way to transform XML input) in the CATALINA environmement file (if Apache Tomcat is used as a java container) , and after adding SAXON library in Tomcat <em>lib <\/em>directory, the way becomes much easier. <br><br><code>set CATALINA_OPTS=%CATALINA_OPTS% -Djavax.xml.transform.TransformerFactory=net.sf.saxon.TransformerFactoryImpl<\/code><br><\/p>\n\n\n\n<p>Once the query has been created and the layout has been associated to, the report can be launched with the following URL:<br><code>f?p=&amp;APP_ID.:0:&amp;SESSION.:PRINT_REPORT=empdept<\/code><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Install database objects<\/h3>\n\n\n\n<p>A DDL script (FOP_DDL)  and a package, BOP_PKG, have to be installed in the worskpace owner schema. <br>cf  <a href=\"https:\/\/github.com\/patrickmonaco\/BIP_TO_FOP\">https:\/\/github.com\/patrickmonaco\/BIP_TO_FOP<\/a> <\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Adding a template repository<\/h3>\n\n\n\n<p>A simple APEX application is provided for <a href=\"https:\/\/github.com\/patrickmonaco\/BIP_TO_FOP\/blob\/master\/f132.sql\">managing templates<\/a>. When a new template BIP template is uploaded, a new converted template is created.<br>At this stage, the lifecycle is:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Update\/Create RTF template with MS Word &amp; BIP Plugin<\/li><li>Export RTF template in XSL-FO (with BIP Word Plugin)<\/li><li>Upload the new version template in BOP repository<\/li><li>Download the converted template<\/li><li>Remove the previous layout<\/li><li>Re-create a new layout with the same name (or not) and upload the converted template<\/li><li>Update the report query to setup the new layout<\/li><\/ul>\n\n\n\n<p>The process is quite cumbersome because on each update, we have to remove then re-create a new layout, then upload the converted wslt-fo template. In order to minimize the tasks, the idea is to synchronise the repository with the apex layouts  internal table. <br>Note: The following step is optionnal. Remove the related code inside the application if this step is to be ignored. <\/p>\n\n\n\n<p>The name of this table is: <code>WWV_FLOW_REPORT_LAYOUTS<\/code><br>The pl\/sql procedure then updates the layout by uploading the template converted previously. The APEX table is: <code>WWV_FLOW_REPORT_LAYOUTS<\/code> .<br>Full access rights on this table must be granted to the workspace owner. <\/p>\n\n\n\n<p>As SYS user, let&rsquo;s grant DML on this table <\/p>\n\n\n\n<pre class=\"wp-block-preformatted\"><code>grant ALL on APEX_&lt;XXX&gt;.WWV_FLOW_REPORT_LAYOUTS TO DEMO;<\/code><\/pre>\n\n\n\n<p>Now the lifecycle becomes:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Update RTF template with MS Word<\/li><li>Export RTF template in XSL-FO (with BIP Word Plugin)<\/li><li>Upload the new version template in BOP repository<\/li><\/ul>\n\n\n\n<p style=\"font-size:-3px\">Because each update in BOP Table will update the conterpart in <code>WWV_FLOW_REPORT_LAYOUTS<\/code> table, no more effort is needed.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Limitations:<\/h3>\n\n\n\n<ul class=\"wp-block-list\"><li>Numerous BIP (xdo) functions are not supported<\/li><li>Wordarts, shapes are not supported<\/li><li>Static graphics  (ie: an image included in the rtf template) encoded in base64 are not supported <\/li><li>Formatting in XPATH expression is supported for number datatype only<\/li><\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Discussion about XML dataset<\/h3>\n\n\n\n<p>Above, this is a XML sample generated by a Report Query.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select emp.*, \ndept.dname, \ndept.loc \nfrom emp, dept \nwhere emp.deptno = dept.deptno <\/pre>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image.png\" alt=\"\" class=\"wp-image-4526\" width=\"389\" height=\"451\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image.png 588w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-258x300.png 258w\" sizes=\"auto, (max-width: 389px) 100vw, 389px\" \/><\/figure>\n\n\n\n<p>We can observe that the dataset is rendered as a single table (a single rowset), in other words, a flat tree. There is no way to build, with a regular report query, a complex tree document. The consequence is that we have to use Group Function <strong>inside <\/strong>the xst-fo template if break page is needed in the final report.<\/p>\n\n\n\n<p>Therefore, because of the previous point, we&rsquo;ll have to rely on XSLT group function in the plugin, for achieving some behaviours like page break. This point explains why we subsitute to XALAN (XSLTC), the SAXON parser which is more suitable.<\/p>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Use cases<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Tabular reports<\/h3>\n\n\n\n<p>There is no difficulty to design a template which produce a tabular report. It&rsquo;s possible to leverage most of the features of BIP Desktop combined with the powerful of MS Word. The page break is managed by the Apache XSL-FO engine.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"293\" height=\"698\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-11.png\" alt=\"\" class=\"wp-image-4591\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-11.png 293w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-11-126x300.png 126w\" sizes=\"auto, (max-width: 293px) 100vw, 293px\" \/><figcaption>Tabular output with conditionnal formating<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Invoices<\/h3>\n\n\n\n<p>It&rsquo;s a very common type of document where there is :<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>a header: (invoice header, customer address, ..)  <\/li><li>one or more set of data multi-lines related to the invoice (product items) <\/li><li>a footer: total and legal mentions, terms and conditions &#8230;<\/li><\/ul>\n\n\n\n<div class=\"wp-block-image\"><figure class=\"alignright is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-9.png\" alt=\"\" class=\"wp-image-4565\" width=\"362\" height=\"309\"\/><\/figure><\/div>\n\n\n\n<p>In the context of Oracle APEX, we must keep in mind that the XML output is flat. We just can leverage multiple rowsets in the same report. That leads to two different ways for building the XML dataset. The first one is to get a single rowset by joing all needed data upon a common key (order_id). The second one is to get a first rowset for the header information and other ones for multiple lines data.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">One occurence of a result set<\/h4>\n\n\n\n<p>Goal: print a single document with a single occurence. ie: An invoice for a given order id.<\/p>\n\n\n\n<p>In this case, the query can contains a single sql statement whichs is a join with all participants tables (order, ords items, products, customer).<br>Or we can combine two queries: one for the header ans on another for the order lines.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">All occurences of a result set<\/h4>\n\n\n\n<p>In this case, the goal is to print a single document which contains all occurences returned by a query with an optional page break between occurences.<\/p>\n\n\n\n<p>Query:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">select emp.*, \ndept.dname, \ndept.loc \nfrom emp, dept \nwhere emp.deptno = dept.deptno \n<\/pre>\n\n\n\n<p>Oracle APEX generates a XML document with the following schema:<\/p>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-1.png\" alt=\"\" class=\"wp-image-4530\" width=\"441\" height=\"428\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-1.png 758w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-1-300x292.png 300w\" sizes=\"auto, (max-width: 441px) 100vw, 441px\" \/><figcaption>XML schema generated by APEX Report Query<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"430\" height=\"702\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-2.png\" alt=\"\" class=\"wp-image-4535\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-2.png 430w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-2-184x300.png 184w\" sizes=\"auto, (max-width: 430px) 100vw, 430px\" \/><figcaption>Output pdf with page break on Department<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"719\" height=\"397\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-3.png\" alt=\"\" class=\"wp-image-4536\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-3.png 719w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-3-300x166.png 300w\" sizes=\"auto, (max-width: 719px) 100vw, 719px\" \/><figcaption>BIP RTF template<br><br><br><br><\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Fonts<\/h2>\n\n\n\n<p>Using specific fonts requires some additionnal setup. We need to adapt ORDS starting parameters.<br>A parameter in ORDS configuration file must be modified for pointing to the FOP configuration file:<\/p>\n\n\n\n<p><code>&lt;entry key=\"<strong>fop.configfile<\/strong>\"&gt;<\/code><br><code>X:\\distrib\\fop-2.2\\fop\\conf\\fopapex.xconf<\/code><br><code>&lt;\/entry&gt; <\/code><\/p>\n\n\n\n<p>Once the server has been restarted, all the requierd fonts will be honored by Apache FOP. Otherwise, there is a fallback mechanism which maps the original fonts on a small subset (Times Roman).<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"653\" height=\"557\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-16.png\" alt=\"\" class=\"wp-image-4622\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-16.png 653w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-16-300x256.png 300w\" sizes=\"auto, (max-width: 653px) 100vw, 653px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-12.png\" alt=\"\" class=\"wp-image-4592\" width=\"512\" height=\"304\"\/><figcaption>Fonts embedded by the pdf document with fonts<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"665\" height=\"549\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-13.png\" alt=\"\" class=\"wp-image-4619\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-13.png 665w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-13-300x248.png 300w\" sizes=\"auto, (max-width: 665px) 100vw, 665px\" \/><\/figure>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-14.png\" alt=\"\" class=\"wp-image-4620\" width=\"471\" height=\"236\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-14.png 729w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-14-300x150.png 300w\" sizes=\"auto, (max-width: 471px) 100vw, 471px\" \/><figcaption>Fonts embedded in the output pdf without fonts<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>The method presented previously shows up a good balance between complexity\/High quality and simplicity\/basic rendition in pdf document design. The BIP desktop plugin gives a very good flexibility provided you get a correct knowledge of  MS Word.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">APPENDIX<\/h2>\n\n\n\n<p>The following sample gives an overview of the XPATH query wich is generated by the BIP desktop plugin. Of course, it&rsquo;s always possible to enrich the behavior by adding some additional XPATH expressions.<\/p>\n\n\n\n<figure class=\"wp-block-image is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-4.png\" alt=\"\" class=\"wp-image-4541\" width=\"587\" height=\"271\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-4.png 727w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-4-300x139.png 300w\" sizes=\"auto, (max-width: 587px) 100vw, 587px\" \/><figcaption>XPATH generated by BIP Desktop plugin<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"740\" height=\"364\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-5.png\" alt=\"\" class=\"wp-image-4546\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-5.png 740w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-5-300x148.png 300w\" sizes=\"auto, (max-width: 740px) 100vw, 740px\" \/><figcaption>PDF output with the FOP engine \/ report with XSLT break<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"703\" height=\"603\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-9.png\" alt=\"\" class=\"wp-image-4565\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-9.png 703w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-9-300x257.png 300w\" sizes=\"auto, (max-width: 703px) 100vw, 703px\" \/><figcaption>PDF output with the APEX FOP engine &#8211; Master detail<\/figcaption><\/figure>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"636\" height=\"612\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-8.png\" alt=\"\" class=\"wp-image-4563\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-8.png 636w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-8-300x289.png 300w\" sizes=\"auto, (max-width: 636px) 100vw, 636px\" \/><figcaption>Simple Invoice RTF template in BIP desktop<\/figcaption><\/figure>\n\n\n\n<h3 class=\"wp-block-heading\">Queries used for invoice demo<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Multi-queries &#8211; two rowsets<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">select <br> o.order_id, <br> o.customer_id,<br> o.order_total,<br> to_char(o.order_timestamp,'DD MONTH YYYY') order_timestamp,<br> c.cust_first_name,<br> c.cust_last_name,<br> c.cust_street_address1,<br> c.cust_street_address2,<br> c.cust_city,<br> c.cust_state,<br> c.cust_postal_code<br> from demo_orders o,<br> demo_customers c<br> where c.customer_id = o.customer_id<br> and o.order_id = 1<br><br>select <br> i.unit_price,<br> i.quantity,<br> round((i.quantity * i.unit_price),2) amount,<br> p.product_name,<br> p.product_description<br> from <br> demo_order_items i,<br> demo_product_info p<br> where p.product_id = i.product_id and<br> i.order_id=1<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"718\" src=\"http:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-10-1024x718.png\" alt=\"\" class=\"wp-image-4567\" srcset=\"https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-10-1024x718.png 1024w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-10-300x210.png 300w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-10-768x538.png 768w, https:\/\/gpmfactory.com\/wp-content\/uploads\/2019\/10\/image-10.png 1093w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><figcaption>Sample multiple queries (rowsets) <\/figcaption><\/figure>\n\n\n\n<h4 class=\"wp-block-heading\">Single Query &#8211; single rowset<\/h4>\n\n\n\n<pre class=\"wp-block-preformatted\">select \n o.order_id, \n o.customer_id,\n o.order_total,\n o.order_timestamp,\n i.unit_price,\n i.quantity,\n p.product_name,\n p.product_description,\n c.cust_first_name,\n c.cust_last_name,\n c.cust_street_address1,\n c.cust_street_address2,\n c.cust_city,\n c.cust_state,\n c.cust_postal_code\n from demo_orders o,\n demo_order_items i,\n demo_customers c,\n demo_product_info p\n where o.order_id = i.order_id and\n p.product_id = i.product_id and\n c.customer_id = o.customer_id<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Leverage the Oracle BI Publisher templating power and produce high fidelity reports Abstract Oracle APEX is a tool which is well suited for rapid&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"ppma_author":[150],"class_list":["post-4525","post","type-post","status-publish","format-standard","hentry","category-non-classe"],"authors":[{"term_id":150,"user_id":1,"is_guest":0,"slug":"admin8700","display_name":"Patrick","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/209d5ed69b74d288390621ab4c1d3773?s=96&d=mm&r=g","0":null,"1":"","2":"","3":"","4":"","5":"","6":"","7":"","8":""}],"_links":{"self":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/4525","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/comments?post=4525"}],"version-history":[{"count":104,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/4525\/revisions"}],"predecessor-version":[{"id":4652,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/posts\/4525\/revisions\/4652"}],"wp:attachment":[{"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/media?parent=4525"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/categories?post=4525"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/tags?post=4525"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/gpmfactory.com\/index.php\/wp-json\/wp\/v2\/ppma_author?post=4525"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}