Building a printserver in PL/SQL for APEX with PLPDF

When our customer purchased a licence for PLPDF I started to think about building a simple printserver for APEX in pure PL/SQL.

That should be simple, right? Well, let’s see.

The first step I took was to investigate the full HTTP request APEX makes to its printerengine.

For this I used node.js. Yes, I could have used any sniffer available and APEX also gives you the XML, but building it myself in node.js was more fun. The catcher writes the request to a textfile named body.txt.

File: index.js

var server = require("./server");
var router = require("./router");


File: server.js
var http = require("http"),
  url = require("url");

var constants = {"Port": "80"};

function start(route) {
  http.createServer(function (request, response) {
    route(url.parse(request.url).pathname, request, response);
  }).listen(constants.Port, function () {
    console.log("Listening on " + constants.Port);

exports.start = start;

File: router.js
var fs = require('fs');

function route(pathname, request, response) {
  console.log('In de router: ');
  console.log('Path: ' + pathname);
  console.log('Method:' + request.method);
  var body = '';

  request.on('data', function (chunck) {
    body += chunck;

  request.on('end', function () {
    fs.writeFile('body.txt', body, function (err) {
      if (err) {
        return console.log(err);
      response.writeHead(200, {"Content-Type": "text/plain"});

exports.route = route;

I started the catcher via

  node index.js

and ajusted the Apex Instance settings as admin to the following:
Print Server : Oracle BI Publisher  –> Apache FOP will also work
Print Server protocol : HTTP
Print Server Host Adress: The hostname of my machine
Print Server Port : 80 The Port where the catcher is listening
Print Server Script : /<dad>/converttopdf

After that I created an application with a Report Query and a simple page with a button to print. How to do that is out of the scope of this blog.

After pressing the print-button the catcher wrote the follwing information:

Path: /<dad>/converttopdf
Method: POST
xml=<?xml version="1.0" encoding="UTF-8"?>
   <REGION ID="some number">
&template=<?xml version ....

So, APEX used a POST method to /<dad>/converttopdf using a body with the following four parameters
xml, template, _xtype, _xf

So, I should be able to create a procedure converttopdf using these named parameters…. not. _xtype and _xf are not allowed as parameternames in PL/SQL. Of course you could do something like

create or replace procedure converttopdf
  ( "xml" in varchar2
  , "template" varchar2
  , "_xtype" in varchar2
  , "_xf" in varchar2

but I tried it and it didn’t work eventually.

So I tried some old school flexible naming:

create or replace procedure converttopdf
  (   name_array  IN  owa.vc_arr
  ,   value_array IN  owa.vc_arr

and altered the Print Server Script setting to:
/<dad>/!converttopdf –> notice the exclamation

Now make sure that APEX_PUBLIC_USER can “see” the procedure (grants, synonyms) and that APEX allows you to call the new procedure in the URL by altering the

apex_xxxx.wwv_flow_epg_include_mod_local function

Now, the only thing we shoud do is altering Print Server Host Adress to the hostname of the webserver.

And it works! When hitting the print button you will get the XML that’s generated by APEX in your procedere. With PLPDF its relatively easy to generate the pdf
in a blob and send it back with the respons:

create or replace procedure converttopdf
(   name_array  IN  owa.vc_arr
,   value_array IN  owa.vc_arr
  /* Proof of concept */
  l_blob          blob;
  l_font          plpdf_type.t_row_font;
  l_heading_font  plpdf_type.t_row_font;
  l_row_aligns    plpdf_type.t_row_aligns;
  l_row_borders   plpdf_type.t_row_borders;
  l_row_datas     plpdf_type.t_row_datas;
  l_row_fonts     plpdf_type.t_row_fonts;
  l_row_maxlines  plpdf_type.t_row_maxlines;
  l_row_widths    plpdf_type.t_row_widths;
  l_header_widths plpdf_type.t_row_widths;
  l_titel         varchar2(2000);
  l_xml           xmltype;
  l_xml_chr       varchar2(32767);
  procedure reset_row
  procedure add_column
    ( p_naam in varchar2
    , p_id   in number
    if p_naam is not null
      l_row_datas(p_id)    := p_naam;
      if NOT l_header_widths.exists(p_id)
        l_row_widths(p_id)   := length(p_naam)*5;
        l_header_widths(p_id) := l_row_widths(p_id);
        l_row_widths(p_id)   := l_header_widths(p_id);
      end if;
      l_row_borders(p_id)  := 1;
      l_row_aligns(p_id)   := 'L';     
      l_row_fonts(p_id)    := l_font;    
      l_row_maxlines(p_id) := 10;     
    end if;  
  end ;
  l_xml_chr := value_array(1);
  l_xml := xmltype(l_xml_chr);
  -- := 'Arial';  := '';
  l_heading_font.fsize  := 10;
    ( p_family =>
    , p_style  => 'B'
    , p_size   => l_heading_font.fsize
  select extractvalue(l_xml,'/DOCUMENT/TITLE') into l_titel from dual;  
  l_titel := coalesce(l_titel,'No title supplied');
    ( p_txt => 'Rapport '||l_titel||' of '
    ||to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') 
  FOR r_rows IN
    ( SELECT rownum as id
      ,      extract(column_value,'/ROW/*[1]').getRootElement()  as column_name_1
      ,      extractvalue(column_value,'/ROW/*[1]')              as column_value_1
      ,      extract(column_value,'/ROW/*[2]').getRootElement()  as column_name_2
      ,      extractvalue(column_value,'/ROW/*[2]')              as column_value_2
      ,      extract(column_value,'/ROW/*[3]').getRootElement()  as column_name_3
      ,      extractvalue(column_value,'/ROW/*[3]')              as column_value_3
      ,      extract(column_value,'/ROW/*[4]').getRootElement()  as column_name_4
      ,      extractvalue(column_value,'/ROW/*[4]')              as column_value_4
      ,      extract(column_value,'/ROW/*[5]').getRootElement()  as column_name_5
      ,      extractvalue(column_value,'/ROW/*[5]')              as column_value_5
      ,      extract(column_value,'/ROW/*[6]').getRootElement()  as column_name_6
      ,      extractvalue(column_value,'/ROW/*[6]')              as column_value_6
      ,      extract(column_value,'/ROW/*[7]').getRootElement()  as column_name_7
      ,      extractvalue(column_value,'/ROW/*[7]')              as column_value_7
      ,      extract(column_value,'/ROW/*[8]').getRootElement()  as column_name_8
      ,      extractvalue(column_value,'/ROW/*[8]')              as column_value_8
      ,      extract(column_value,'/ROW/*[9]').getRootElement()  as column_name_9
      ,      extractvalue(column_value,'/ROW/*[9]')              as column_value_9
      ,      extract(column_value,'/ROW/*[10]').getRootElement()  as column_name_10
      ,      extractvalue(column_value,'/ROW/*[10]')              as column_value_10
      FROM   TABLE(xmlsequence(l_xml.extract('//DOCUMENT/REGION/ROWSET/ROW'))) t
    if = 1
        ( p_family =>
        , p_style  => 'B'
        , p_size   => l_heading_font.fsize
      add_column(initcap(r_rows.column_name_1),1); add_column(initcap(r_rows.column_name_6),6);
      add_column(initcap(r_rows.column_name_2),2); add_column(initcap(r_rows.column_name_7),7);
      add_column(initcap(r_rows.column_name_3),3); add_column(initcap(r_rows.column_name_8),8);
      add_column(initcap(r_rows.column_name_4),4); add_column(initcap(r_rows.column_name_9),9);
      add_column(initcap(r_rows.column_name_5),5); add_column(initcap(r_rows.column_name_10),10);
        ( p_data    => l_row_datas
        , p_border  => l_row_borders
        , p_width   => l_row_widths
        , p_align   => l_row_aligns
        , p_font    => l_row_fonts
        , p_maxline => l_row_maxlines
        ( p_family =>
        , p_style  => null
        , p_size   => l_heading_font.fsize
    end if;
    add_column(r_rows.column_value_1,1); add_column(r_rows.column_value_6,6);
    add_column(r_rows.column_value_2,2); add_column(r_rows.column_value_7,7);
    add_column(r_rows.column_value_3,3); add_column(r_rows.column_value_8,8);
    add_column(r_rows.column_value_4,4); add_column(r_rows.column_value_9,9);
    add_column(r_rows.column_value_5,5); add_column(r_rows.column_value_10,10);                        
    ( p_data    => l_row_datas
    , p_border  => l_row_borders
    , p_width   => l_row_widths
    , p_align   => l_row_aligns
    , p_font    => l_row_fonts
    , p_maxline => l_row_maxlines
  owa_util.mime_header('application/pdf', false);
  htp.p('Content-Disposition: attachment; filename="'||l_titel||'.pdf"');
  htp.p('Content-Length: ' || dbms_lob.getlength(l_blob));
  when others

What are the drawbacks?

Just look at the definition of the parameters of converttopdf. The array can just hold up to 32K values.
If you try to print something that’s more than 32K in size it won’t work. But for some simple reports for which the input data
doesn’t become larger that 32K, this concept does work.


2 thoughts on “Building a printserver in PL/SQL for APEX with PLPDF

  1. Very slick!
    We implemented PLPDF in an app a few years ago and it was a lot of work to create a complex report layout. But even the tabular reports took a bunch of time. I’d have KILLED for a callable engine like this that converted a simple report into PDF.
    Thanks for sharing.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s