PL/SQL API

Pre-requisites

APEX 5.0.4 or higher needs to be installed in the database as the PL/SQL API uses some packages that come with APEX e.g. apex_json.

Install in the Database

Go into SQL Plus, SQLcl, SQL Developer or SQL Workshop and make sure following packages exist aop_api_pkg, aop_plsql20_pkg, the synonyms aop_api_pkg and aop_plsql_pkg. As part of the installation those packages should have been compiled.

How to use

You can call AOP straight from PL/SQL. APEX Office Print comes with two different ways of calling AOP; one package is aop_api_pkg which is also used behind the scenes in the APEX Plug-in and the other one is aop_plsql_pkg. aop_plsql_pkg is especially useful when you just want to do the call to the AOP server component or cloud.

Next to the above two packages we also provide two additional packages that show how to do the pl/sql calls. See the packages aop_sample_pkg and aop_test_pkg.

Parameters

aop_api_pkg

Global variables which can be set

--### Logger
g_logger_enabled             boolean := true;        -- In case you use Logger (https://github.com/OraOpenSource/Logger), you can compile this package to enable Logger output:
                                                     -- SQL> ALTER PACKAGE aop_api21_pkg COMPILE PLSQL_CCFLAGS = 'logger_on:TRUE';
                                                     -- When compiled and this global variable is set to true, debug will be written to logger too
--### Call to AOP 
g_aop_url                    varchar2(200) := null;  -- AOP Server url
g_api_key                    varchar2(50)  := null;  -- AOP API Key; only needed when AOP Cloud is used (http(s)://www.apexofficeprint.com/api)
g_aop_mode                   varchar2(15)  := null;  -- AOP Mode can be development or production; when running in development no cloud credits are used but a watermark is printed
g_failover_aop_url           varchar2(200) := null;  -- AOP Server url in case of failure of AOP url
g_failover_procedure         varchar2(200) := null;  -- When the failover url is used, the procedure specified in this variable will be called
g_template_type              varchar2(100) := null;  -- Specify the template type (xlsx, docx, ...) in case the filename is not part of the template source (e.g. URL of OneDrive or Object Storage)
g_output_converter           varchar2(50)  := null;  -- Set the converter to go to PDF (or other format different from template) e.g. officetopdf, libreoffice or libreoffice-standalone
g_output_correct_page_nr     boolean       := false; -- boolean to check for AOPMergePage text to replace it with the page number.
g_output_lock_form           boolean       := false; -- boolean that determines if the pdf forms should be locked/flattened.
g_identify_form_fields       boolean       := false; -- boolean that fills in the name of the fields of a PDF Form in the field itself so it's easy to identify which field is at what position
g_proxy_override             varchar2(300) := null;  -- null=proxy defined in the application attributes
g_transfer_timeout           number(6)     := 1800;  -- default of APEX is 180
g_wallet_path                varchar2(300) := null;  -- null=defined in Manage Instance > Instance Settings
g_wallet_pwd                 varchar2(300) := null;  -- null=defined in Manage Instance > Instance Settings
g_https_host                 varchar2(300) := null;  -- The host name to be matched against the common name (CN) of the remote server's certificate for an HTTPS request.
g_output_filename            varchar2(300) := null;  -- output
g_cloud_provider             varchar2(30)  := null;  -- dropbox, gdrive, onedrive, aws_s3, (s)ftp
g_cloud_location             varchar2(300) := null;  -- directory in dropbox, gdrive, onedrive, aws_s3 (with bucket), (s)ftp
g_cloud_access_token         varchar2(500) := null;  -- access token or credentials for dropbox, gdrive, onedrive, aws_s3, (s)ftp (needs json)
g_language                   varchar2(2)   := c_en;  -- Language can be: en, fr, nl, de, used for the translation of filters applied etc. (translation build-in AOP)
g_app_language               varchar2(20)  := null;  -- Language specified in the APEX app (primary language, translated language), when left to null, apex_util.get_session_lang is being used
g_logging                    clob          := '';    -- ability to add your own logging: e.g. "request_id":"123", "request_app":"APEX", "request_user":"RND"
g_debug                      varchar2(10)  := null;  -- set to 'Local' when only the JSON needs to be generated, 'Remote' for remore debug
g_debug_procedure            varchar2(4000):= null;  -- when debug in APEX is turned on, next to the normal APEX debug, this procedure will be called
                                                     --   e.g. to write to your own debug table. The definition of the procedure needs to be the same as aop_debug
g_app_id                     number        := null;  -- APEX application id
g_page_id                    number        := null;  -- APEX page id
g_user_name                  varchar2(200) := null;  -- APEX user name (APP_USER)
--### APEX Page Items 
g_apex_items                 varchar2(4000):= null;  -- colon separated list of APEX items e.g. P1_X:P1_Y, which can be referenced in a template using {Pxx_ITEM}
                                                     -- you can only use this global variable in combination with reports (classic, IR, IG, ...).
                                                     -- When using a SQL Query, you can define the page item in your SQL query, e.g. :P1_ITEM as "P1_ITEM"
--### Layout for IR  
g_rpt_header_font_name       varchar2(50)  := '';    -- Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163
g_rpt_header_font_size       varchar2(3)   := '';    -- 14
g_rpt_header_font_color      varchar2(50)  := '';    -- #071626
g_rpt_header_back_color      varchar2(50)  := '';    -- #FAFAFA
g_rpt_header_border_width    varchar2(50)  := '';    -- 1 ; '0' = no border
g_rpt_header_border_color    varchar2(50)  := '';    -- #000000
g_rpt_data_font_name         varchar2(50)  := '';    -- Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163
g_rpt_data_font_size         varchar2(3)   := '';    -- 14
g_rpt_data_font_color        varchar2(50)  := '';    -- #000000
g_rpt_data_back_color        varchar2(50)  := '';    -- #FFFFFF
g_rpt_data_border_width      varchar2(50)  := '';    -- 1 ; '0' = no border
g_rpt_data_border_color      varchar2(50)  := '';    -- #000000
g_rpt_data_alt_row_color     varchar2(50)  := '';    -- #FFFFFF for no alt row color, use same color as g_rpt_data_back_color
/* see also Printing attributes in Interactive Report */
--### Settings for Calendar
g_cal_type                   varchar2(10)  := c_cal_month; -- can be month (default), week, day, list; constants can be used
g_start_date                 date          := null;  -- start date of calendar
g_end_date                   date          := null;  -- end date of calendar
g_weekdays                   varchar2(300) := null;  -- translation for weekdays e.g. Monday:Tuesday:Wednesday etc.
g_months                     varchar2(300) := null;  -- translation for months   e.g. January:February etc.  
g_color_days_sql             varchar2(4000):= null;  -- color the background of certain days.
                                                     --   e.g. select 1 as "id", sysdate as "date", 'FF8800' as "color" from dual
g_separate_pages             varchar2(5)   := 'false'; -- start calendar on new page (true) or start calendar on same page
g_alignment                  varchar2(5)   := 'right'; -- align text on calender: left center or right
g_title_alignment            varchar2(5)   := 'right'; -- align title of the calendar: left right or center
g_day_alignment              varchar2(5)   := 'right'; -- align days of the calendar: left right or center
g_start_of_week              varchar2(3)   := 'Mon';   -- start of the week day: Monday (Mon) or Sunday (Sun)
--### HTML template to Word/PDF
g_orientation                varchar2(50)  := '';    -- empty is portrait, other option is 'landscape'
--### Call to URL data source
g_url_http_method            varchar2(10)  := 'GET';
g_url_username               varchar2(300) := null;
g_url_password               varchar2(300) := null;
g_url_schema                 varchar2(100) := 'Basic';
g_url_proxy_override         varchar2(300) := null;
g_url_transfer_timeout       number        := 180;
g_url_body                   clob          := empty_clob();
g_url_body_blob              blob          := empty_blob();
g_url_parm_name              apex_application_global.vc_arr2; --:= empty_vc_arr;
g_url_parm_value             apex_application_global.vc_arr2; --:= empty_vc_arr;
g_url_wallet_path            varchar2(300) := null;
g_url_wallet_pwd             varchar2(300) := null;
g_url_https_host             varchar2(300) := null;  -- parameter for apex_web_service, not used, please apply APEX patch if issues
g_url_credential_static_id   varchar2(300) := null;
g_url_token_url              varchar2(300) := null;
--### Web Source Module (APEX >= 18.1)
g_web_source_first_row       pls_integer   := null;  -- parameter for apex_exec.open_web_source_query
g_web_source_max_rows        pls_integer   := null;  -- parameter for apex_exec.open_web_source_query
g_web_source_total_row_cnt   boolean       := false; -- parameter for apex_exec.open_web_source_query
--### REST Enabled SQL (APEX >= 18.1)
g_rest_sql_auto_bind_items   boolean       := true;  -- parameter for apex_exec.open_remote_sql_query
g_rest_sql_first_row         pls_integer   := null;  -- parameter for apex_exec.open_remote_sql_query
g_rest_sql_max_rows          pls_integer   := null;  -- parameter for apex_exec.open_remote_sql_query
g_rest_sql_total_row_cnt     boolean       := false; -- parameter for apex_exec.open_remote_sql_query
g_rest_sql_total_row_limit   pls_integer   := null;  -- parameter for apex_exec.open_remote_sql_query
--### Input Data
g_replace_special_symbols    varchar2(5)   := null;  -- Option to replace special symbols in the selected columns/keys. Replaces +, -, *, /, and  % by _.
--### IP Printer support
g_ip_printer_location        varchar2(300) := null;
g_ip_printer_version         varchar2(300) := '1';
g_ip_printer_requester       varchar2(300) := nvl(apex_application.g_user, USER);
g_ip_printer_job_name        varchar2(300) := 'AOP';
g_ip_printer_return_output   varchar2(5)   := null;  -- null or 'Yes' or 'true'
--### AOP Processing
g_pre_conversion_command     varchar2(4000):= null; -- The command to execute before the conversion to another file format. This command should be present on aop_config.json file.
g_pre_conversion_command_p   varchar2(4000):= null; -- Parameter (in JSON) before the conversion to another file format. These parameters should be present on aop_config.json file.
g_post_conversion_command    varchar2(4000):= null; -- The command to execute after the conversion to another file format. This command should be present on aop_config.json file.
g_post_conversion_command_p  varchar2(4000):= null; -- Parameter (in JSON) after the conversion to another file format. These parameters should be present on aop_config.json file.
g_post_merge_command         varchar2(4000):= null; -- The command to execute after the merge of files. This command should be present on aop_config.json file.
g_post_merge_command_p       varchar2(4000):= null; -- Parameter (in JSON) after the merge of files. These parameters should be present on aop_config.json file.
g_pipeline_name              varchar2(4000):= null; -- The name of the pipeline that will be executed.
g_post_process_command       varchar2(4000):= null; -- The command to execute. This command should be present on aop_config.json file.
g_post_process_command_p     varchar2(4000):= null; -- Parameter (in JSON) in the post process command. These parameters should be present on aop_config.json file.
g_post_process_return_output boolean       := true; -- Either to return the output or not. Note this output is AOP's output and not the post process command output.
g_post_process_delete_delay  number(9)     := 1500; -- AOP deletes the file provided to the command directly after executing it. This can be delayed with this option. Integer in milliseconds.
--### AOP Config
g_aop_config                 varchar2(32767):= null; -- AOP config file; anything here will overwrite or extend other attributes in the JSON. Make sure this is valid JSON.
--### Convert characterset 
g_convert                    varchar2(1)   := c_n;   -- set to Y (c_y) if you want to convert the JSON that is send over; necessary for Arabic support
g_convert_source_charset     varchar2(20)  := null;  -- default of database
g_convert_target_charset     varchar2(20)  := 'AL32UTF8';  
g_stop_apex_engine           varchar2(1)   := c_n;   -- stop the APEX engine
--### Output
-- set output directory on AOP Server
-- if . is specified the files are saved in the default directory: outputfiles
g_output_directory           varchar2(200) := '.';   
g_output_sign_certificate    varchar2(32000):= null; -- sign PDF with signature which is base64 encoded
g_output_sign_certificate_pwd varchar2(500):= null;  -- sign PDF with password
g_output_split               varchar2(5)   := null;  -- split file: one file per page: true/false
g_output_merge               varchar2(5)   := null;  -- merge files into one PDF true/false
g_output_icon_font           varchar2(20)  := null;  -- the icon font to use for the output, Font-APEX or Font Awesome 5 (default)
g_output_even_page           varchar2(5)   := null;  -- PDF option to always print even pages (necessary for two-sided pages): true/false
g_output_merge_making_even   varchar2(5)   := null;  -- PDF option to merge making all documents even paged (necessary for two-sided pages): true/false
g_output_page_margin         varchar2(50)  := null;  -- HTML to PDF option: margin in px, can also add top, bottom, left, right
g_output_page_orientation    varchar2(10)  := null;  -- HTML to PDF option: portrait (default) or landscape
g_output_page_width          varchar2(10)  := null;  -- HTML to PDF option: width in px, mm, cm, in. No unit means px.
g_output_page_height         varchar2(10)  := null;  -- HTML to PDF option: height in px, mm, cm, in. No unit means px.
g_output_page_format         varchar2(10)  := null;  -- HTML to PDF option: a4 (default), letter
g_output_remove_last_page    boolean       := false; -- PDF option to remove the last page; e.g. when the last page is empty
--### Background
g_run_in_background          varchar2(1)   := c_n;   -- Run the call in the background through a dbms_scheduler job, when finished call defined procedure. 
g_is_component_used_yn       varchar2(1)   := null;  -- If you want to override the is_component_used_yn, you can specify 'Y' to always show or 'N' to never show.
g_visible_report_columns     varchar2(4000):= null;  -- Colon separated list of classic report, interactive report or interactive grid columns e.g. EMPNO:ENAME,
                                                     -- which will be visible regardless of authorization and condition
g_hidden_report_columns      varchar2(4000):= null;  -- Colon separated list of classic report, interactive report or interactive grid columns e.g. EMPNO:ENAME
                                                     -- which will be hidden regardless of authorization and condition

--### Files
g_prepend_files_sql          clob          := null;  -- format: select filename, mime_type, [file_blob, file_base64, url_call_from_db, url_call_from_aop, file_on_aop_server] from my_table
g_append_files_sql           clob          := null;  -- format: select filename, mime_type, [file_blob, file_base64, url_call_from_db, url_call_from_aop, file_on_aop_server] from my_table
g_media_files_sql            clob          := null;  --  
g_output_prepend_per_page    boolean       := false; -- Prepend one or more pages before each page in the output. E.g. logo and company details before every document
g_output_append_per_page     boolean       := false; -- Append one or more pages after each page in the output. E.g. terms of conditions after every invoice

--### Templates
g_template_start_delimiter   varchar2(2)   := null;  -- { is the default start delimiter used is a template, but you can set this variable with the following options: {, {{, <, <<
g_template_end_delimiter     varchar2(2)   := null;  -- } is the default end delimiter used in a template, but you can set this variable with the following options: }, }}, >, >>
g_cache_template             boolean       := false; -- cache the template; an hash is returned in g_template_cache_hash
g_template_cache_hash        varchar2(128) := null;  -- the hashed value of the cached version of the template on the AOP Server/Cloud
g_use_template_when_no_cache varchar2(1)   := c_n;   -- by default when a template hash is sent and it's no longer available it will raise an error.
                                                     -- when set to Y(es), AOP will first check if the template is still available and if not include the full template when available.

--### Sub-Templates
g_sub_templates_sql          clob          := null;  -- format: select filename, mime_type, [file_blob, file_base64, url_call_from_db, url_call_from_aop, file_on_aop_server] from my_table

--### Password protected PDF
g_output_read_password       varchar2(200) := null; -- protect PDF to read
g_output_modify_password     varchar2(200) := null; -- protect PDF to write (modify)
g_output_pwd_protection_flag number(4)     := null; -- optional; default is 4. 
                                                    -- Number when bit calculation is done as specified in http://pdfhummus.com/post/147451287581/hummus-1058-and-pdf-writer-updates-encryption
g_output_watermark           varchar2(4000):= null; -- Watermark in PDF
g_output_watermark_color     varchar2(500) := null; -- Watermark option color
g_output_watermark_font      varchar2(500) := null; -- Watermark option font
g_output_watermark_width     varchar2(500) := null; -- Watermark option width
g_output_watermark_height    varchar2(500) := null; -- Watermark option height
g_output_watermark_opacity   varchar2(500) := null; -- Watermark option opacity
g_output_watermark_rotation  varchar2(500) := null; -- Watermark option rotation
g_output_copies              number        := null; -- Requires output pdf, repeats the output pdf for the given number of times.

--### IG
g_ig_force_query             varchar2(1)   := null; -- force the IG to use AOPs own implementation instead of apex_region.open_query_context
g_ig_use_alternative_label   varchar2(1)   := null; -- force the IG to use the alternative label for the heading

--### JSON
g_anonymize_json             varchar2(1)   := c_n;   -- set to Y (c_y) if you want to anomyze/obfuscate the JSON that is send over. This is great for debugging of sensitive data.
g_use_data_export_pjson      varchar2(1)   := c_n;   -- instead of using the AOP specific code to generate the meta-data of reports, use apex_data_export.c_format_pjson

--### CSV
g_output_text_delimiter      varchar2(200) := null;  -- 
g_output_field_separator     varchar2(200) := null;  -- 
g_output_character_set       varchar2(200) := null;  -- 

--### DATA EXPORT - APEX 20.2 and higher
$if wwv_flow_api.c_current >= 20201001
$then 
g_data_export_component_id   number                         := null;
g_data_export_view_mode      varchar2(100)                  := null;
g_data_export_max_rows       number                         := null;
g_data_export_file_name      varchar2(255)                  := null;
g_data_export_page_size      apex_data_export.t_size        := apex_data_export.c_size_letter;
g_data_export_orientation    apex_data_export.t_orientation := apex_data_export.c_orientation_portrait;
g_data_export_data_only      boolean                        := false;
g_data_export_pdf_accessible boolean                        := false;  
$end  

--### OCI
g_oci_credential             varchar2(150) := null;  -- Credentials used in DBMS_CLOUD (Oracle Cloud Infrastructure credentials)
g_oci_directory_name         varchar2(150) := null;  -- Directory name used in DBMS_CLOUD 

Function call

function plsql_call_to_aop(
  p_data_type                 in varchar2 default c_source_type_sql,
  p_data_source               in clob     default null,
  p_template_type             in varchar2 default c_source_type_apex,
  p_template_source           in clob     default null,
  p_output_type               in varchar2 default c_pdf_pdf,
  p_output_filename           in out nocopy varchar2,
  p_output_type_item_name     in varchar2 default null,
  p_output_to                 in varchar2 default null,
  p_procedure                 in varchar2 default null,
  p_binds                     in wwv_flow_plugin_util.t_bind_list default c_binds,
  p_special                   in varchar2 default null,
  p_aop_remote_debug          in varchar2 default c_no,
  p_output_converter          in varchar2 default null,
  p_aop_url                   in varchar2 default null,
  p_api_key                   in varchar2 default null,
  p_aop_mode                  in varchar2 default null,
  p_app_id                    in number   default null,
  p_page_id                   in number   default null,
  p_user_name                 in varchar2 default null,
  p_init_code                 in clob     default c_init_null,
  p_output_encoding           in varchar2 default c_output_encoding_raw,
  p_output_split              in varchar2 default c_false,
  p_output_merge              in varchar2 default c_false,
  p_output_even_page          in varchar2 default c_false,
  p_output_merge_making_even  in varchar2 default c_false,
  p_failover_aop_url          in varchar2 default null,
  p_failover_procedure        in varchar2 default null,
  p_log_procedure             in varchar2 default null,
  p_prepend_files_sql         in clob     default null,
  p_append_files_sql          in clob     default null,
  p_media_files_sql           in clob     default null,
  p_sub_templates_sql         in clob     default null,
  p_ref_cursor                in sys_refcursor default null,
  p_sql_array                 in t_query_list default c_sql_array,
  p_ig_selected_pks           in varchar2 default null)
  return blob;

Note that some other procedures and functions are available in the package which could be helpful.

aop_plsql_pkg

function make_aop_request(
  p_aop_url            in varchar2 default g_aop_url,
  p_api_key            in varchar2 default g_api_key,
  p_aop_mode           in varchar2 default g_aop_mode,  
  p_json               in clob,
  p_template           in blob,
  p_template_type      in varchar2 default null,
  p_output_encoding    in varchar2 default 'raw', -- change to raw to have binary, change to base64 to have base64 encoded
  p_output_type        in varchar2 default null,
  p_output_filename    in varchar2 default 'output',
  p_aop_remote_debug   in varchar2 default 'No',
  p_output_converter   in varchar2 default '',
  p_prepend_files_json in clob default null,
  p_append_files_json  in clob default null)
  return blob;

Example

Example with aop_api_pkg

declare
    l_binds           wwv_flow_plugin_util.t_bind_list;
    l_return          blob;
    l_output_filename varchar2(100) := 'output';
begin
    -- define bind variables
    l_binds(1).name := 'p_id';
    l_binds(1).value := '1';

    l_return := aop_api_pkg.plsql_call_to_aop (
                p_data_type       => 'SQL',
                p_data_source     => q'[
                    select
                    'file1' as "filename",
                    cursor(
                        select
                        c.cust_first_name as "cust_first_name",
                        c.cust_last_name as "cust_last_name",
                        c.cust_city as "cust_city",
                        cursor(select o.order_total as "order_total",
                                        'Order ' || rownum as "order_name",
                                    cursor(select p.product_name as "product_name",
                                                i.quantity as "quantity",
                                                i.unit_price as "unit_price", APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as "image"
                                            from demo_order_items i, demo_product_info p
                                            where o.order_id = i.order_id
                                            and i.product_id = p.product_id
                                        ) "product"
                                    from demo_orders o
                                where c.customer_id = o.customer_id
                                ) "orders"
                        from demo_customers c
                        where customer_id = :p_id
                    ) as "data"
                    from dual
                ]',
                p_template_type   => 'SQL',
                p_template_source => q'[
                    select template_type, template_blob
                    from aop_template
                    where id = 1
                ]',
                p_output_type     => 'docx',
                p_output_filename => l_output_filename,
                p_binds           => l_binds,
                p_aop_url         => 'http://api.apexofficeprint.com/',
                p_api_key         => 'your API key',
                p_app_id          => 232);
end;

Example with aop_plsql_pkg

declare
    l_template        blob;
    l_output_file     blob;
begin
    select template_blob
    into l_template
    from aop_template
    where id = 1;

    l_output_file := aop_plsql_pkg.make_aop_request(
                        p_json        => '[{ "filename": "file1", "data": [{ "cust_first_name": "APEX Office Print" }] }]',
                        p_template    => l_template,
                        p_output_type => 'docx',
                        p_aop_remote_debug => 'Yes');
end;

In aop_sample_pkg you find the above examples and you can run them with following command:

begin
    aop_sample_pkg.call_aop_plsql_pkg;
    aop_sample_pkg.call_aop_api_pkg;
end;

Note that there are more examples in the aop_sample_pkg, so it's worthwhile to check it out.

PL/SQL API Documentation

The complete reference can be found in the AOP PL/SQL API documentation.