While I love Oracle Application Express, I have experienced frustration with generating stunning printed material. APEX Office Print (AOP) has simultaneously expanded our team’s capabilities while reducing our effort. That’s a tool! Rather, that is the definition of a great tool. Our ancestors used antlers to dig in the dirt, then someone came along with the steel spade. I feel the same way about AOP. In a previous entry, we touched on the structure of the SQL needed to prepare the data for AOP. Three months later, I still refer back to this blog for quick answers and structures. In this blog, we will explore AOP Text Formatting.

In the recent months, our team has:

  1. Created data-rich reports with grouping, sub-totals, running totals all formatted in MS Excel and handed back as PDF
  2. Developed text-rich reports that generate glorious MS Word documents — Easy and affordable database driven Word documents
  3. Added both conditional formatting and conditional data to reports


Edit AOP Report For one of our commercial applications (Tempest-Bid), we create proposals. Customers use our software to respond to government requests for proposals. These are highly structured documents with hundreds (thousands) of building blocks, often involve multiple technical writers, dozens of resumes, and an output that must be perfect. For the purpose of this example and blog, we are simplifying the proposal down. We use the US Constitution as a sample of the process. And for this blog, we’ll show it being assembled in Oracle APEX and AOP.


We are tool-builders. We pride ourselves on great support, but… we really don’t want to have to bill a customer because they want to change a footer, a font, or some other formatting within a report. Let’s give that power back to the user. AOP now let’s us share this with our customers. Want Arial 12? Go for it! Want narrower margins? Help yourself!

Not answers that have been routinely available within APEX.

In this entry, we will focus on

  • Sharing Template management with the customer
  • Linking AOP query with Templates


Our report menus are data driven (dynamic list). The basics are the source page (menu page) and the destination page (APEX page hosting the report). Incorporating AOP to this process means adding the fields needed to support the AOP plugin. Recently, we added a separate child table for report templates. We can load templates from our menu, identifying templates as “Global” or customers can add their own templates, which are then identified as “Private”.

AOP is also data driven. The data for the AOP report derives from a query. But so does the identification of the template. This means that you can provide a select list of available templates to your users. To get there, we needed a means of managing templates.

AOP Report Template

Open the collapsible region to review, edit, or add new templates to a report.

AOP Report Template List


When writing an AOP query, we adopt the same thinking we use on Interactive Reports. What are ALL of the options that the user may want? What fields should we prepare for them? What formatting is required? The challenge for us is to anticipate needs down the road.


And we need to prepare a template with full instructions on it for the user. We call this (in the data and in the image above) “Example”. We put all of the fields with definitions. We put instructions and coaching.

Preparing CLOBs

There is a trick when managing text in CLOBs. The data exchange between Oracle and AOP is done in JSON. The hand off strips the carriage return/line feed that maintains the integrity of paragraphs and lists. The guys at AOP look for a \n in the data, then substitute the CR/LF. It is for us to prepare the data with the \n.

  ,replace(replace(sn.snippet_clob, chr(10), ''), chr(13), ' \n ')                "snippet"

This replace statement pulls the CR/LF — chr(10) chr(13) — from the data and lays in the \n as required by AOP.


The Output

The following image is a report generated from data within Oracle, managed by APEX and “printed” by AOP into MS Word.

US Constitution


While the example shown uses three fields, there are more available for the user on this report. Because we are curious geeks, here is the underlying query:

	'file1' as "filename",
  cursor ( select
     cursor (
  ,c.client_name                "client"
  ,p.proposal_name              "proposal"
  ,                       "require_name"
  ,r.description                "require_description"
  ,r.wbs_sequence               "wbs_sequence"
  ,r.sequence_order             "require_sequence"
  ,r.proposal_section           "proposal_section"
  ,r.proposal_section_title     "section_title"
  ,(select first_name || ' ' || last_name from bid_asset where asset_pk = sn.submit_asset_fk and rownum = 1) "submit_first_last"
  ,(select last_name || ', ' || first_name from bid_asset where asset_pk = sn.submit_asset_fk and rownum = 1) "submit_last_first"
  ,sn.TITLE                       "snippet_title"
  ,replace(replace(sn.snippet_clob, chr(10), ''), chr(13), ' \n ')                "snippet"
  ,sn.APPROVED                    "approved"
  ,to_char(sn.CREATED_ON,'MM/DD/YYYY')  "created_on"
  ,sn.CREATED_BY                        "created_by"
  ,to_char(sn.UPDATED_ON,'MM/DD/YYYY')  "updated_on"
  ,sn.UPDATED_BY                        "updated_by"
  ,r.outline_sequence                   "outl_seq"
from bid_require_snippet sn
left join bid_client c on
  c.client_pk = sn.client_fk
left join bid_proposal p on
  p.proposal_pk = sn.proposal_fk
left join bid_require r on
  r.require_pk = sn.require_fk
where nvl(sn.archived,'N') <> 'Y'
  and nvl(sn.approved,'N') = 'Y'
  and sn.proposal_fk = :G_PROPOSAL_PK
order by r.outline_sequence, r.proposal_section 
    ) as "d"
 from dual ) as "data"
from dual

Example Template

With users being able to download templates, modify them, upload modifications or new ones, we wanted to provide instructions. The image below illustrates one of these “examples”.

US Constitution