AOP PL/SQL - API Documentation

aop_api19_pkg

Created by Dimitri Gielis on 12/12/2015
APEX Office Print (AOP) package that holds the PL/SQL API, which is being called by the APEX Plug-in, but which can also be called manually.

Constants

Template and Data Type

c_source_type_apex
c_source_type_apex constant varchar2(4) := 'APEX'

Template Type

c_source_type_workspace
c_source_type_workspace constant varchar2(9) := 'WORKSPACE'

Template Type

c_source_type_sql
c_source_type_sql constant varchar2(3) := 'SQL'

Template and Data Type

c_source_type_plsql_sql
c_source_type_plsql_sql constant varchar2(9) := 'PLSQL_SQL'

Template and Data Type

c_source_type_plsql
c_source_type_plsql constant varchar2(5) := 'PLSQL'

Template and Data Type

c_source_type_url
c_source_type_url constant varchar2(3) := 'URL'

Template and Data Type

c_source_type_url_aop
c_source_type_url_aop constant varchar2(7) := 'URL_AOP'

Template Type

c_source_type_rpt
c_source_type_rpt constant varchar2(6) := 'IR'

Data Type

c_source_type_xml
c_source_type_xml constant varchar2(3) := 'XML'

Data Type

c_source_type_json
c_source_type_json constant varchar2(4) := 'JSON'

Data Type

c_source_type_filename
c_source_type_filename constant varchar2(8) := 'FILENAME'

Template Type

c_source_type_aop_report
c_source_type_aop_report constant varchar2(20) := 'AOP_REPORT'

Template Type

c_source_type_layouts
c_source_type_layouts constant varchar2(15) := 'REPORT_LAYOUTS'

Template Type

Converter

c_source_type_converter
c_source_type_converter constant varchar2(9) := 'CONVERTER'

c_converter_libreoffice
c_converter_libreoffice constant varchar2(7) := 'soffice'

LibreOffice

c_converter_msoffice
c_converter_msoffice constant varchar2(11) := 'officetopdf'

MS Office (only Windows)

c_converter_custom
c_converter_custom constant varchar2(7) := 'custom'

Custom converter defined in the AOP Server config

Mime Type

c_mime_type_docx
c_mime_type_docx constant varchar2(71) := 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'

c_mime_type_xlsx
c_mime_type_xlsx constant varchar2(65) := 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'

c_mime_type_pptx
c_mime_type_pptx constant varchar2(73) := 'application/vnd.openxmlformats-officedocument.presentationml.presentation'

c_mime_type_pdf
c_mime_type_pdf constant varchar2(15) := 'application/pdf'

c_mime_type_html
c_mime_type_html constant varchar2(9) := 'text/html'

c_mime_type_markdown
c_mime_type_markdown constant varchar2(13) := 'text/markdown'

c_mime_type_rtf
c_mime_type_rtf constant varchar2(15) := 'application/rtf'

c_mime_type_json
c_mime_type_json constant varchar2(16) := 'application/json'

c_mime_type_text
c_mime_type_text constant varchar2(10) := 'text/plain'

c_mime_type_csv
c_mime_type_csv constant varchar2(10) := 'text/csv'

c_mime_type_png
c_mime_type_png constant varchar2(9) := 'image/png'

c_mime_type_jpg
c_mime_type_jpg constant varchar2(10) := 'image/jpeg'

c_mime_type_gif
c_mime_type_gif constant varchar2(9) := 'image/gif'

c_mime_type_bmp
c_mime_type_bmp constant varchar2(9) := 'image/bmp'

c_mime_type_msbmp
c_mime_type_msbmp constant varchar2(19) := 'image/x-windows-bmp'

c_mime_type_docm
c_mime_type_docm constant varchar2(48) := 'application/vnd.ms-word.document.macroenabled.12'

c_mime_type_xlsm
c_mime_type_xlsm constant varchar2(46) := 'application/vnd.ms-excel.sheet.macroenabled.12'

c_mime_type_pptm
c_mime_type_pptm constant varchar2(58) := 'application/vnd.ms-powerpoint.presentation.macroenabled.12'

c_mime_type_ics
c_mime_type_ics constant varchar2(13) := 'text/calendar'

c_mime_type_ifb
c_mime_type_ifb constant varchar2(13) := 'text/calendar'

Calender Type

c_cal_month
c_cal_month constant varchar2(19) := 'month'

c_cal_week
c_cal_week constant varchar2(19) := 'week'

c_cal_day
c_cal_day constant varchar2(19) := 'day'

c_cal_list
c_cal_list constant varchar2(19) := 'list'

Output Encoding

c_output_encoding_raw
c_output_encoding_raw constant varchar2(3) := 'raw'

c_output_encoding_base64
c_output_encoding_base64 constant varchar2(6) := 'base64'

Output Type

c_word_docx
c_word_docx constant varchar2(4) := 'docx'

c_excel_xlsx
c_excel_xlsx constant varchar2(4) := 'xlsx'

c_powerpoint_pptx
c_powerpoint_pptx constant varchar2(4) := 'pptx'

c_pdf_pdf
c_pdf_pdf constant varchar2(3) := 'pdf'

c_html_html
c_html_html constant varchar2(4) := 'html'

c_markdown_md
c_markdown_md constant varchar2(2) := 'md'

c_text_txt
c_text_txt constant varchar2(3) := 'txt'

c_csv_csv
c_csv_csv constant varchar2(3) := 'csv'

c_word_rtf
c_word_rtf constant varchar2(3) := 'rtf'

c_word_macro_docm
c_word_macro_docm constant varchar2(4) := 'docm'

c_excel_macro_xlsm
c_excel_macro_xlsm constant varchar2(4) := 'xlsm'

c_powerpoint_macro_pptm
c_powerpoint_macro_pptm constant varchar2(4) := 'pptm'

c_calendar_ics
c_calendar_ics constant varchar2(3) := 'ics'

c_calendar_ifb
c_calendar_ifb constant varchar2(3) := 'ifb'

c_onepagepdf_pdf
c_onepagepdf_pdf constant varchar2(10) := 'onepagepdf'

c_count_tags
c_count_tags constant varchar2(10) := 'count_tags'

c_defined_by_apex_item
c_defined_by_apex_item constant varchar2(9) := 'apex_item'

Output To

c_output_browser
c_output_browser constant varchar2(1) := null

c_output_procedure
c_output_procedure constant varchar2(9) := 'PROCEDURE'

c_output_procedure_browser
c_output_procedure_browser constant varchar2(17) := 'PROCEDURE_BROWSER'

c_output_inline
c_output_inline constant varchar2(14) := 'BROWSER_INLINE'

c_output_directory
c_output_directory constant varchar2(9) := 'DIRECTORY'

c_output_cloud
c_output_cloud constant varchar2(5) := 'CLOUD'

Special

c_special_number_as_string
c_special_number_as_string constant varchar2(16) := 'NUMBER_TO_STRING'

c_special_report_as_label
c_special_report_as_label constant varchar2(16) := 'REPORT_AS_LABELS'

c_special_ir_filters_top
c_special_ir_filters_top constant varchar2(14) := 'FILTERS_ON_TOP'

c_special_ir_highlights_top
c_special_ir_highlights_top constant varchar2(17) := 'HIGHLIGHTS_ON_TOP'

c_special_ir_excel_header_f
c_special_ir_excel_header_f constant varchar2(18) := 'HEADER_WITH_FILTER'

c_special_ir_saved_report
c_special_ir_saved_report constant varchar2(19) := 'ALWAYS_REPORT_ALIAS'

c_special_ir_repeat_header
c_special_ir_repeat_header constant varchar2(13) := 'repeat_header'

Debug

c_debug_remote
c_debug_remote constant varchar2(3) := 'Yes'

c_debug_local
c_debug_local constant varchar2(5) := 'Local'

c_debug_application_item
c_debug_application_item constant varchar2(9) := 'APEX_ITEM'

Mode

c_mode_production
c_mode_production constant varchar2(15) := 'production'

c_mode_development
c_mode_development constant varchar2(15) := 'development'

Supported Languages; used for the translation of IR

c_en
c_en constant varchar2(5) := 'en'

c_nl
c_nl constant varchar2(5) := 'nl'

c_fr
c_fr constant varchar2(5) := 'fr'

c_de
c_de constant varchar2(5) := 'de'

Others

c_aop_version
c_aop_version constant varchar2(5) := '19.2'

The version of APEX Office Print (AOP)

c_aop_url
c_aop_url constant varchar2(50) := 'http://api.apexofficeprint.com/'

The default url for the AOP Server for https use https://api.apexofficeprint.com/

c_aop_url_fallback
c_aop_url_fallback constant varchar2(50) := 'http://www.cloudofficeprint.com/aop/'

The default url for the AOP Fallback Server in case the c_aop_url would fail for https use https://www.cloudofficeprint.com/aop/

c_init_null
c_init_null constant varchar2(5) := 'null;'

c_false
c_false constant varchar2(5) := 'false'

c_true
c_true constant varchar2(4) := 'true'

c_yes
c_yes constant varchar2(3) := 'Yes'

c_no
c_no constant varchar2(2) := 'No'

c_y
c_y constant varchar2(1) := 'Y'

c_n
c_n constant varchar2(1) := 'N'

c_apex_050
c_apex_050 constant pls_integer := 20130101

c_apex_051
c_apex_051 constant pls_integer := 20160824

c_apex_181
c_apex_181 constant pls_integer := 20180404

c_apex_191
c_apex_191 constant pls_integer := 20190331

c_apex_192
c_apex_192 constant pls_integer := 20190930

Variables

Logger

g_logger_enabled
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_api19_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
g_aop_url varchar2(100) := null

AOP Server url

g_api_key
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
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
g_failover_aop_url varchar2(100) := null

AOP Server url in case of failure of AOP url

g_failover_procedure
g_failover_procedure varchar2(200) := null

When the failover url is used, the procedure specified in this variable will be called

g_output_converter
g_output_converter varchar2(50) := null

Set the converter to go to PDF (or other format different from template) e.g. officetopdf or libreoffice

g_output_correct_page_nr
g_output_correct_page_nr boolean := false

boolean to check for AOPMergePage text to replace it with the page number.

g_output_lock_form
g_output_lock_form boolean := false

boolean that determines if the pdf forms should be locked/flattened.

g_proxy_override
g_proxy_override varchar2(300) := null

null=proxy defined in the application attributes

g_transfer_timeout
g_transfer_timeout number(6) := 1800

default of APEX is 180

g_wallet_path
g_wallet_path varchar2(300) := null

null=defined in Manage Instance > Instance Settings

g_wallet_pwd
g_wallet_pwd varchar2(300) := null

null=defined in Manage Instance > Instance Settings

g_output_filename
g_output_filename varchar2(300) := null

output

g_cloud_provider
g_cloud_provider varchar2(30) := null

dropbox, gdrive, onedrive, aws_s3

g_cloud_location
g_cloud_location varchar2(300) := null

directory in dropbox, gdrive, onedrive, aws_s3 (with bucket)

g_cloud_access_token
g_cloud_access_token varchar2(500) := null

access token for dropbox, gdrive, onedrive, aws_s3 (needs json)

g_language
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
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
g_logging clob := ''

ability to add your own logging: e.g. "request_id":"123", "request_app":"APEX", "request_user":"RND"

g_debug
g_debug varchar2(10) := null

set to 'Local' when only the JSON needs to be generated, 'Remote' for remore debug

g_debug_procedure
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

APEX Page Items

g_apex_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}

Layout for IR

g_rpt_header_font_name
g_rpt_header_font_name varchar2(50) := ''

Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163

g_rpt_header_font_size
g_rpt_header_font_size varchar2(3) := ''

14

g_rpt_header_font_color
g_rpt_header_font_color varchar2(50) := ''

#071626

g_rpt_header_back_color
g_rpt_header_back_color varchar2(50) := ''

#FAFAFA

g_rpt_header_border_width
g_rpt_header_border_width varchar2(50) := ''

1 ; '0' = no border

g_rpt_header_border_color
g_rpt_header_border_color varchar2(50) := ''

#000000

g_rpt_data_font_name
g_rpt_data_font_name varchar2(50) := ''

Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163

g_rpt_data_font_size
g_rpt_data_font_size varchar2(3) := ''

14

g_rpt_data_font_color
g_rpt_data_font_color varchar2(50) := ''

#000000

g_rpt_data_back_color
g_rpt_data_back_color varchar2(50) := ''

#FFFFFF

g_rpt_data_border_width
g_rpt_data_border_width varchar2(50) := ''

1 ; '0' = no border

g_rpt_data_border_color
g_rpt_data_border_color varchar2(50) := ''

#000000

g_rpt_data_alt_row_color
g_rpt_data_alt_row_color varchar2(50) := ''

#FFFFFF for no alt row color, use same color as g_rpt_data_back_color

Settings for Calendar

g_cal_type
g_cal_type varchar2(10) := c_cal_month

can be month (default), week, day, list; constants can be used

g_start_date
g_start_date date := null

start date of calendar

g_end_date
g_end_date date := null

end date of calendar

g_weekdays
g_weekdays varchar2(300) := null

translation for weekdays e.g. Monday:Tuesday:Wednesday etc.

g_months
g_months varchar2(300) := null

translation for months e.g. January:February etc.

g_color_days_sql
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

HTML template to Word/PDF

g_orientation
g_orientation varchar2(50) := ''

empty is portrait, other option is 'landscape'

Call to URL data source

g_url_username
g_url_username varchar2(300) := null

g_url_password
g_url_password varchar2(300) := null

g_url_proxy_override
g_url_proxy_override varchar2(300) := null

g_url_transfer_timeout
g_url_transfer_timeout number := 180

g_url_body
g_url_body clob := empty_clob()

g_url_body_blob
g_url_body_blob blob := empty_blob()

g_url_parm_name
g_url_parm_name apex_application_global.vc_arr2; -- := empty_vc_arr

g_url_parm_value
g_url_parm_value apex_application_global.vc_arr2; -- := empty_vc_arr

g_url_wallet_path
g_url_wallet_path varchar2(300) := null

g_url_wallet_pwd
g_url_wallet_pwd varchar2(300) := null

g_url_https_host
g_url_https_host varchar2(300) := null

parameter for apex_web_service, not used, please apply APEX patch if issues

Web Source Module (APEX >= 18.1)

g_web_source_first_row
g_web_source_first_row pls_integer := null

parameter for apex_exec.open_web_source_query

g_web_source_max_rows
g_web_source_max_rows pls_integer := null

parameter for apex_exec.open_web_source_query

g_web_source_total_row_cnt
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
g_rest_sql_auto_bind_items boolean := true

parameter for apex_exec.open_remote_sql_query

g_rest_sql_first_row
g_rest_sql_first_row pls_integer := null

parameter for apex_exec.open_remote_sql_query

g_rest_sql_max_rows
g_rest_sql_max_rows pls_integer := null

parameter for apex_exec.open_remote_sql_query

g_rest_sql_total_row_cnt
g_rest_sql_total_row_cnt boolean := false

parameter for apex_exec.open_remote_sql_query

g_rest_sql_total_row_limit
g_rest_sql_total_row_limit pls_integer := null

parameter for apex_exec.open_remote_sql_query

IP Printer support

g_ip_printer_location
g_ip_printer_location varchar2(300) := null

g_ip_printer_version
g_ip_printer_version varchar2(300) := '1'

g_ip_printer_requester
g_ip_printer_requester varchar2(300) := nvl(apex_application.g_user, USER)

g_ip_printer_job_name
g_ip_printer_job_name varchar2(300) := 'AOP'

g_ip_printer_return_output
g_ip_printer_return_output varchar2(5) := null

null or 'Yes' or 'true'

Post Processing

g_post_process_command
g_post_process_command varchar2(100) := null

The command to execute. This command should be present on aop_config.json file.

g_post_process_return_output
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
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
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
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
g_convert_source_charset varchar2(20) := null

default of database

g_convert_target_charset
g_convert_target_charset varchar2(20) := 'AL32UTF8'

Output

g_output_directory
g_output_directory varchar2(200) := '.'

set output directory on AOP Server if . is specified the files are saved in the default directory: outputfiles

g_output_split
g_output_split varchar2(5) := null

split file: one file per page: true/false

g_output_icon_font
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
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
g_output_merge_making_even varchar2(5) := null

PDF option to merge making all documents even paged (necessary for two-sided pages): true/false

Files

g_prepend_files_sql
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]

g_append_files_sql
g_append_files_sql clob := null

from my_table

Sub-Templates

g_sub_templates_sql
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
g_output_read_password varchar2(200) := null

protect PDF to read

g_output_modify_password
g_output_modify_password varchar2(200) := null

protect PDF to write (modify)

g_output_pwd_protection_flag
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
g_output_watermark varchar2(4000) := null

Watermark in PDF

Procedure AOP_DEBUG

Created by Dimitri Gielis on 2016-7-19
wrapper around APEX debug, Logger and own Debug procedure defined in g_debug_procedure

Syntax

procedure aop_debug(p_message     in varchar2, 
                    p0            in varchar2 default null, 
                    p1            in varchar2 default null, 
                    p2            in varchar2 default null, 
                    p3            in varchar2 default null, 
                    p4            in varchar2 default null, 
                    p5            in varchar2 default null, 
                    p6            in varchar2 default null, 
                    p7            in varchar2 default null, 
                    p8            in varchar2 default null, 
                    p9            in varchar2 default null, 
                    p10           in varchar2 default null, 
                    p11           in varchar2 default null, 
                    p12           in varchar2 default null, 
                    p13           in varchar2 default null, 
                    p14           in varchar2 default null, 
                    p15           in varchar2 default null, 
                    p16           in varchar2 default null, 
                    p17           in varchar2 default null, 
                    p18           in varchar2 default null, 
                    p19           in varchar2 default null, 
                    p_level       in apex_debug.t_log_level default apex_debug.c_log_level_info, 
                    p_description in clob default null)

Parameters

  • p_message The debug message. Occurrences of '%s' is replaced by p0 to p19.
  • p_level The log level for the message, default is c_log_level_info. -- p_level information c_log_level_error constant apex_debug.t_log_level := 1; -- critical error c_log_level_warn constant apex_debug.t_log_level := 2; -- less critical error c_log_level_info constant apex_debug.t_log_level := 4; -- default level if debugging is enabled (for example, used by apex_application.debug) c_log_level_app_enter constant apex_debug.t_log_level := 5; -- application: messages when procedures/functions are entered c_log_level_app_trace constant apex_debug.t_log_level := 6; -- application: other messages within procedures/functions c_log_level_engine_enter constant apex_debug.t_log_level := 8; -- Application Express engine: messages when procedures/functions are entered c_log_level_engine_trace constant apex_debug.t_log_level := 9; -- Application Express engine: other messages within procedures/functions
  • p_description Can hold large data (clob), will be stored in Logger p_extra or in p_description of your own logging

Example

  aop_debug(p_message => 'Assert condition not met: %s', p0 => p_message, p_level => apex_debug.c_log_level_error);

Function URL2BASE64

Created by Dimitri Gielis on 2016-8-2
convert a url with for example an image to base64

Syntax

function url2base64 (
  p_url in varchar2)
  return clob

Parameters

  • p_url URL to be encoded to base64

  • Return

  • base64 encoded string

Function GETMIMETYPE

Created by Dimitri Gielis on 2016-8-2
get the mime type of a file extension: docx, xlsx, pptx, pdf, html, md, json, txt, csv, jpg, gif, png, bmp

Syntax

function getmimetype (
  p_file_ext in varchar2)
  return varchar2 deterministic

Parameters

  • p_file_ext Enter the extension e.g. docx

  • Return

  • Get the mime type e.g. application/vnd.openxmlformats-officedocument.wordprocessingml.document

Function GETFILEEXTENSION

Created by Dimitri Gielis on 2016-8-2
get the file extension of a mime type

Syntax

function getfileextension (
  p_mime_type in varchar2)
  return varchar2 deterministic

Parameters

  • p_mime_type Enter the mime type e.g. application/vnd.openxmlformats-officedocument.wordprocessingml.document

  • Return

  • returns the extension e.g. docx

Function BLOB2CLOB

Created by Dimitri Gielis on 2016-8-2
BLOB to CLOB Conversion

Syntax

function blob2clob(p_blob in blob) return clob

Parameters

  • p_blob Binary file

  • Return

  • Character representation

Function PLSQL_CALL_TO_AOP

Created by Dimitri Gielis on 2016-8-2
Call to AOP Server through API, used behind the scenes by the APEX plug-in, but a manual call can be done with PL/SQL too.

Syntax

function plsql_call_to_aop(
  p_data_type                 in varchar2 default c_source_type_sql,
  p_data_source               in clob,
  p_template_type             in varchar2 default c_source_type_apex,
  p_template_source           in clob,
  p_output_type               in varchar2,
  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,
  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_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_sub_templates_sql         in clob     default null
  )  
  return blob

Parameters

  • p_data_type Define where the data is coming from. Following constants exists in aop_api_pkg: c_source_type_sql, c_source_type_plsql_sql, c_source_type_plsql, c_source_type_url, c_source_type_rpt
  • p_data_source Depending the data type, define here the source: - c_source_type_sql: SQL statement returning mime type and blob - c_source_type_plsql_sql: PL/SQL function returning SQL statement with mime type and blob - c_source_type_plsql: PL/SQL function returning JSON with the template file base64 encoded - c_source_type_url: URL which contains the file - c_source_type_rpt: static id(s) of the APEX regions
  • p_template_type Define where the template is stored. Following constants exists in aop_api_pkg: c_source_type_apex, c_source_type_workspace, c_source_type_sql, c_source_type_plsql_sql, c_source_type_plsql, c_source_type_url, c_source_type_filename
  • p_template_source Depending the template_type, define here the filename, SQL statement, PL/SQL function or URL: - c_source_type_apex: file uploaded in APEX Static Application Files - c_source_type_workspace: file uploaded in APEX Workspace Files - c_source_type_sql: SQL statement returning mime type and blob - c_source_type_plsql_sql: PL/SQL function returning SQL statement with mime type and blob - c_source_type_plsql: PL/SQL function returning JSON with the template file base64 encoded - c_source_type_url: URL which contains the file (will be read from DB server) - c_source_type_url_aop: URL which contains the file (will be read from AOP server) - c_source_type_filename: file defined on the AOP Server - c_source_type_layouts: Report Layouts defined in Oracle APEX > Shared Components
  • p_output_type Extension (pdf, xlsx, ...) or mime type (application/pdf, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, ...) of the output format. Following constants exists in aop_api_pkg: - c_word_docx - c_excel_xlsx - c_powerpoint_pptx - c_pdf_pdf - c_html_html - c_markdown_md - c_text_txt - c_csv_csv - c_word_rtf - c_onepagepdf_pdf - c_count_tags - c_defined_by_apex_item
  • p_output_filename Filename of the result
  • p_output_type_item_name APEX Item holding the filename
  • p_output_to Where does the blob or file need to be sent to: - c_output_browser: the browser will open the file - c_output_procedure: a blob will be passed to a procedure which is defined in p_procedure - c_output_procedure_browser: a blob will be passed to a procedure which is defined in p_procedure and the file is sent to the browser - c_output_inline: the output is defined for showing inline in a region - c_output_directory: the file is stored on the AOP Server in this directory - c_output_cloud: a file is sent to the cloud (Dropbox, Amazon S3, Google Drive) using the credentials defined in g_cloud_provider, g_cloud_location and g_cloud_access_token
  • p_procedure Procedure that needs to be called when the file is merged
  • p_binds Bind variable for SQL or PL/SQL Source
  • p_special Special settings defined in the APEX Plug-in concerning Reports. Following constants can be used: - c_special_number_as_string - c_special_report_as_label - c_special_ir_filters_top - c_special_ir_highlights_top - c_special_ir_excel_header_f - c_special_ir_saved_report - c_special_ir_repeat_header
  • p_aop_remote_debug Turning debugging on will generate the JSON that is sent to the AOP Server in a file. The actual request to the AOP Server is not done. Following constants can be used: - c_debug_remote: store the JSON in your dashboard on https://www.apexofficeprint.com - c_debug_local: store the JSON local on your pc - c_debug_application_item: depending the Application item AOP_DEBUG, Remote (Yes) or Local (Local) or no debugging is done
  • p_output_converter Define the PDF converter you want to use. Multiple converters can be defined in the AOP Server. e.g. officetopdf or libreoffice
  • p_aop_url Description: URL where the AOP Server is running. For the AOP Cloud use c_aop_url
  • p_api_key Description: API Key which can be found when you login at https://www.apexofficeprint.com
  • p_app_id APEX Application ID
  • p_page_id Page ID to call in the APEX application
  • p_user_name Username which should be used to create an APEX session
  • p_init_code Initialisation code which can be invoked in this package
  • p_output_encoding Following constants can be used: c_output_encoding_raw, c_output_encoding_base64
  • p_output_split Description

  • Return

  • blob in defined output format containing result of merged template(s) with data and prepend and append files.

Procedure CREATE_APEX_SESSION

Created by Dimitri Gielis on 2016-8-2
create an APEX session

Syntax

procedure create_apex_session(
  p_app_id       in apex_applications.application_id%type,
  p_user_name    in apex_workspace_sessions.user_name%type default 'ADMIN',
  p_page_id      in apex_application_pages.page_id%type default null,
  p_session_id   in apex_workspace_sessions.apex_session_id%type default null,
  p_enable_debug in varchar2 default 'No')

Parameters

  • p_app_id Description
  • p_user_name Description
  • p_page_id Description
  • p_session_id Description
  • p_enable_debug Description

Function GET_APEX_SESSION

Created by Dimitri Gielis on 2016-8-2
get an APEX session

Syntax

function get_apex_session
  return apex_workspace_sessions.apex_session_id%type

Parameters


    Return

  • Description

Procedure JOIN_APEX_SESSION

Created by Dimitri Gielis on 2016-8-2
join an APEX session

Syntax

procedure join_apex_session(
  p_session_id   in apex_workspace_sessions.apex_session_id%type,
  p_app_id       in apex_applications.application_id%type default null,
  p_page_id      in apex_application_pages.page_id%type default null,
  p_enable_debug in varchar2 default 'No')

Parameters

  • p_session_id Description
  • p_app_id Description
  • p_page_id Description
  • p_enable_debug Description

Procedure DROP_APEX_SESSION

Created by Dimitri Gielis on 2016-8-2
drop an APEX session

Syntax

procedure drop_apex_session(
  p_app_id     in apex_applications.application_id%type default null,
  p_session_id in apex_workspace_sessions.apex_session_id%type default null)

Parameters

  • p_app_id Description
  • p_session_id Description