Renduva documentation

PL/SQL package

The renduva package calls the server from the database and returns the document as a BLOB. It works from APEX pages, plain PL/SQL and scheduler jobs. It uses APEX_WEB_SERVICE, so it needs APEX installed in the database (any supported release).

Install

1. Let the database reach the server. As a DBA, allow both the schema that will own the package and the APEX schema (APEX_ plus the release, e.g. APEX_240200) to connect to the Renduva host and port. The APEX schema needs it because it makes the HTTP call:

begin
  for p in (select column_value u from table(sys.odcivarchar2list('APP_SCHEMA', 'APEX_240200'))) loop
    dbms_network_acl_admin.append_host_ace(
      host => 'renduva.internal', lower_port => 8080, upper_port => 8080,
      ace  => xs$ace_type(privilege_list => xs$name_list('http'),
                          principal_name => p.u, principal_type => xs_acl.ptype_db));
  end loop;
end;
/

2. Install the package as APP_SCHEMA, which needs CREATE PROCEDURE:

SQL> @renduva_install.sql

For an https:// server, the certificate must be trusted by the wallet APEX uses (Instance Settings → Wallet in APEX administration).

Use

declare
  l_pdf blob;
begin
  renduva.set_server('http://renduva.internal:8080', 'rdv_...');

  -- a template stored on the server
  l_pdf := renduva.generate(
             p_template => 'invoice',
             p_data     => '{"customer": "City of Example", "items": []}');

  -- or a template you keep in the database (e.g. an APEX file upload)
  l_pdf := renduva.generate(
             p_template    => l_docx_blob,
             p_data        => l_json,
             p_output_type => 'docx',
             p_filename    => 'contract.docx');
end;
/

p_data is one JSON object for one document, or a JSON array of objects for several records merged into one PDF.

set_server lasts for the database session. APEX does not keep a session between page requests, so call it in an application process (Before Header), or at the start of a job.

Errors

Every failure raises ORA-20100 with the HTTP status and the server's message:

ORA-20100: renduva: HTTP 401: missing or invalid API key
ORA-20100: renduva: HTTP 404: no stored template named "nope"

Building the JSON in SQL

Oracle's JSON functions build the data straight from your tables:

select json_object(
         'customer' value c.name,
         'invoice'  value i.invoice_no,
         'date'     value to_char(i.invoice_date, 'DD Mon YYYY'),
         'items'    value (select json_arrayagg(
                                    json_object('description' value l.description,
                                                'quantity'    value l.quantity,
                                                'price'       value to_char(l.price, 'FM999G990D00'))
                                    order by l.line_no returning clob)
                             from invoice_lines l
                            where l.invoice_id = i.id)
         returning clob)
  into l_json
  from invoices i join customers c on c.id = i.customer_id
 where i.id = :P1_INVOICE_ID;

Format numbers and dates in SQL (TO_CHAR), as above: Renduva prints values exactly as it receives them. For several invoices in one PDF, wrap the query in json_arrayagg.

Images stored as BLOBs go in with renduva.image, which returns the base64 the template's image tags expect (and null for a null BLOB):

json_object('customer' value c.name,
            'logo'     value renduva.image(c.logo)
            returning clob)

Download the PDF from an APEX page

In a page process (or a button's Execute Server-side Code):

declare
  l_pdf blob;
begin
  l_pdf := renduva.generate(p_template => 'invoice', p_data => l_json);
  sys.htp.init;
  sys.owa_util.mime_header('application/pdf', false);
  sys.htp.p('Content-Disposition: attachment; filename="invoice.pdf"');
  sys.owa_util.http_header_close;
  sys.wpg_docload.download_file(l_pdf);
  apex_application.stop_apex_engine;
end;