AOP_API24_PKG
- Constants
- Variables
- AOP_DEBUG Procedure
- CLOB2BASE64 Function
- BASE642CLOB Function
- URL2BASE64 Function
- GETMIMETYPE Function
- GETFILEEXTENSION Function
- GETICON Function
- GET_BINDS Function
- BLOB2CLOB Function
- CLOB2BLOB Function
- BLOB2FILE Procedure
- FILE2BLOB Function
- IS_VALID_OUTPUT_TYPE Function
- IS_VALID_TEMPLATE_HASH Function
- CREATE_APEX_SESSION Procedure
- GET_APEX_SESSION Function
- JOIN_APEX_SESSION Procedure
- DROP_APEX_SESSION Procedure
- PLSQL_CALL_TO_AOP Function
Constants
AOP Version
-- c_aop_version : The version of APEX Office Print (AOP)
c_aop_version constant varchar2(6 char) := '24.2.2'
AOP URLs
-- c_aop_url : The default url for the AOP Server
c_aop_url constant varchar2(50 char) := 'http://api.apexofficeprint.com/'
-- c_aop_url_fallback : The default url for the AOP Fallback Server in case the c_aop_url would fail
c_aop_url_fallback constant varchar2(50 char) := 'https://api-eu.apexofficeprint.com/'
-- c_aop_url_secure : The default secure url for the AOP Server
c_aop_url_secure constant varchar2(50 char) := 'https://api.apexofficeprint.com/'
-- c_aop_url_secure_fallback : The default secure url for the AOP Fallback Server
c_aop_url_secure_fallback constant varchar2(50 char) := 'https://api-eu.apexofficeprint.com/'
-- c_aop_url_oci_us : The url for the AOP Server in the Oracle Cloud US (Ashburn)
c_aop_url_oci_us constant varchar2(50 char) := 'https://api-us.apexofficeprint.com/'
-- c_aop_url_oci_eu : The url for the AOP Server in the Oracle Cloud EU (Frankfurt)
c_aop_url_oci_eu constant varchar2(50 char) := 'https://api-eu.apexofficeprint.com/'
-- c_aop_url_oci_apac : The url for the AOP Server in the Oracle Cloud APAC (Hyperdad)
c_aop_url_oci_apac constant varchar2(50 char) := 'https://api-apac.apexofficeprint.com/'
Template and Data Type
-- c_source_type_apex : Template Type
c_source_type_apex constant varchar2(4 char) := 'APEX'
-- c_source_type_workspace : Template Type
c_source_type_workspace constant varchar2(9 char) := 'WORKSPACE'
-- c_source_type_sql : Template and Data Type
c_source_type_sql constant varchar2(3 char) := 'SQL'
-- c_source_type_plsql_sql : Template and Data Type
c_source_type_plsql_sql constant varchar2(9 char) := 'PLSQL_SQL'
-- c_source_type_plsql : Template and Data Type
c_source_type_plsql constant varchar2(5 char) := 'PLSQL'
-- c_source_type_url : Template and Data Type
c_source_type_url constant varchar2(3 char) := 'URL'
-- c_source_type_url_aop : Template Type
c_source_type_url_aop constant varchar2(7 char) := 'URL_AOP'
-- c_source_type_rpt : Data Type
c_source_type_rpt constant varchar2(6 char) := 'IR'
-- c_source_type_xml : Data Type
c_source_type_xml constant varchar2(3 char) := 'XML'
-- c_source_type_json : Template and Data Type
c_source_type_json constant varchar2(4 char) := 'JSON'
-- c_source_type_json_files : Data Type
c_source_type_json_files constant varchar2(10 char) := 'JSON_FILES'
-- c_source_type_refcursor : Data Type
c_source_type_refcursor constant varchar2(9 char) := 'REFCURSOR'
-- c_source_type_sql_array : Data Type
c_source_type_sql_array constant varchar2(9 char) := 'SQL_ARRAY'
-- c_source_type_filename : Template Type
c_source_type_filename constant varchar2(8 char) := 'FILENAME'
-- c_source_type_db_directory : Template Type
c_source_type_db_directory constant varchar2(12 char) := 'DB_DIRECTORY'
-- c_source_type_aop_report : Template Type
c_source_type_aop_report constant varchar2(10 char) := 'AOP_REPORT'
-- c_source_type_apex_report : Template Type
c_source_type_apex_report constant varchar2(11 char) := 'APEX_REPORT'
-- c_source_type_apex_report_do : Template Type
c_source_type_apex_report_do constant varchar2(14 char) := 'APEX_REPORT_DO'
-- c_source_type_layouts : Template Type
c_source_type_layouts constant varchar2(14 char) := 'REPORT_LAYOUTS'
-- c_source_type_aop_template : Template Type
c_source_type_aop_template constant varchar2(1 char) := null
-- c_source_type_clob_base64 : Template Type
c_source_type_clob_base64 constant varchar2(11 char) := 'CLOB_BASE64'
-- c_source_type_oci_objs : Template Type
c_source_type_oci_objs constant varchar2(8 char) := 'OCI_OBJS'
-- c_source_type_none : Template and Data Type
c_source_type_none constant varchar2(4 char) := 'NONE'
Converter
-- c_source_type_converter
c_source_type_converter constant varchar2(9 char) := 'CONVERTER'
-- c_converter_libreoffice : LibreOffice
c_converter_libreoffice constant varchar2(7 char) := 'soffice'
-- c_converter_libreoffice_sa : LibreOffice Standalone
c_converter_libreoffice_sa constant varchar2(18 char) := 'soffice-standalone'
-- c_converter_msoffice : MS Office (only Windows)
c_converter_msoffice constant varchar2(11 char) := 'officetopdf'
-- c_converter_custom : Custom converter defined in the AOP Server config
c_converter_custom constant varchar2(7 char) := 'custom'
Mime Type
-- c_mime_type_docx
c_mime_type_docx constant varchar2(71 char) := 'application/vnd.openxmlformats-officedocument.wordprocessingml.document'
-- c_mime_type_xlsx
c_mime_type_xlsx constant varchar2(65 char) := 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
-- c_mime_type_pptx
c_mime_type_pptx constant varchar2(73 char) := 'application/vnd.openxmlformats-officedocument.presentationml.presentation'
-- c_mime_type_doc
c_mime_type_doc constant varchar2(71 char) := 'application/msword'
-- c_mime_type_xls
c_mime_type_xls constant varchar2(71 char) := 'application/vnd.ms-excel'
-- c_mime_type_ppt
c_mime_type_ppt constant varchar2(71 char) := 'application/vnd.ms-powerpoint'
-- c_mime_type_odt
c_mime_type_odt constant varchar2(39 char) := 'application/vnd.oasis.opendocument.text'
-- c_mime_type_ods
c_mime_type_ods constant varchar2(46 char) := 'application/vnd.oasis.opendocument.spreadsheet'
-- c_mime_type_odp
c_mime_type_odp constant varchar2(47 char) := 'application/vnd.oasis.opendocument.presentation'
-- c_mime_type_pdf
c_mime_type_pdf constant varchar2(15 char) := 'application/pdf'
-- c_mime_type_html
c_mime_type_html constant varchar2(9 char) := 'text/html'
-- c_mime_type_markdown
c_mime_type_markdown constant varchar2(13 char) := 'text/markdown'
-- c_mime_type_rtf
c_mime_type_rtf constant varchar2(15 char) := 'application/rtf'
-- c_mime_type_json
c_mime_type_json constant varchar2(16 char) := 'application/json'
-- c_mime_type_xml
c_mime_type_xml constant varchar2(15 char) := 'application/xml'
-- c_mime_type_text
c_mime_type_text constant varchar2(10 char) := 'text/plain'
-- c_mime_type_csv
c_mime_type_csv constant varchar2(10 char) := 'text/csv'
-- c_mime_type_png
c_mime_type_png constant varchar2(9 char) := 'image/png'
-- c_mime_type_jpg
c_mime_type_jpg constant varchar2(10 char) := 'image/jpeg'
-- c_mime_type_gif
c_mime_type_gif constant varchar2(9 char) := 'image/gif'
-- c_mime_type_bmp
c_mime_type_bmp constant varchar2(9 char) := 'image/bmp'
-- c_mime_type_msbmp
c_mime_type_msbmp constant varchar2(19 char) := 'image/x-windows-bmp'
-- c_mime_type_docm
c_mime_type_docm constant varchar2(48 char) := 'application/vnd.ms-word.document.macroenabled.12'
-- c_mime_type_xlsm
c_mime_type_xlsm constant varchar2(46 char) := 'application/vnd.ms-excel.sheet.macroenabled.12'
-- c_mime_type_pptm
c_mime_type_pptm constant varchar2(58 char) := 'application/vnd.ms-powerpoint.presentation.macroenabled.12'
-- c_mime_type_ics
c_mime_type_ics constant varchar2(13 char) := 'text/calendar'
-- c_mime_type_ifb
c_mime_type_ifb constant varchar2(13 char) := 'text/calendar'
-- c_mime_type_eml
c_mime_type_eml constant varchar2(14 char) := 'message/rfc822'
-- c_mime_type_msg
c_mime_type_msg constant varchar2(26 char) := 'application/vnd.ms-outlook'
-- c_mime_type_zip
c_mime_type_zip constant varchar2(26 char) := 'application/zip'
Calender Type
-- c_cal_month
c_cal_month constant varchar2(19 char) := 'month'
-- c_cal_week
c_cal_week constant varchar2(19 char) := 'week'
-- c_cal_day
c_cal_day constant varchar2(19 char) := 'day'
-- c_cal_list
c_cal_list constant varchar2(19 char) := 'list'
Output Encoding
-- c_output_encoding_raw
c_output_encoding_raw constant varchar2(3 char) := 'raw'
-- c_output_encoding_base64
c_output_encoding_base64 constant varchar2(6 char) := 'base64'
Output Type
-- c_word_docx
c_word_docx constant varchar2(4 char) := 'docx'
-- c_excel_xlsx
c_excel_xlsx constant varchar2(4 char) := 'xlsx'
-- c_powerpoint_pptx
c_powerpoint_pptx constant varchar2(4 char) := 'pptx'
-- c_opendocument_odt
c_opendocument_odt constant varchar2(3 char) := 'odt'
-- c_opendocument_ods
c_opendocument_ods constant varchar2(3 char) := 'ods'
-- c_opendocument_odp
c_opendocument_odp constant varchar2(3 char) := 'odp'
-- c_word_doc
c_word_doc constant varchar2(3 char) := 'doc'
-- c_excel_xls
c_excel_xls constant varchar2(3 char) := 'xls'
-- c_powerpoint_ppt
c_powerpoint_ppt constant varchar2(3 char) := 'ppt'
-- c_pdf_pdf
c_pdf_pdf constant varchar2(3 char) := 'pdf'
-- c_html_html
c_html_html constant varchar2(4 char) := 'html'
-- c_markdown_md
c_markdown_md constant varchar2(2 char) := 'md'
-- c_text_txt
c_text_txt constant varchar2(3 char) := 'txt'
-- c_csv_csv
c_csv_csv constant varchar2(3 char) := 'csv'
-- c_word_rtf
c_word_rtf constant varchar2(3 char) := 'rtf'
-- c_word_macro_docm
c_word_macro_docm constant varchar2(4 char) := 'docm'
-- c_excel_macro_xlsm
c_excel_macro_xlsm constant varchar2(4 char) := 'xlsm'
-- c_powerpoint_macro_pptm
c_powerpoint_macro_pptm constant varchar2(4 char) := 'pptm'
-- c_calendar_ics
c_calendar_ics constant varchar2(3 char) := 'ics'
-- c_calendar_ifb
c_calendar_ifb constant varchar2(3 char) := 'ifb'
-- c_json_json
c_json_json constant varchar2(4 char) := 'json'
-- c_xml_xml
c_xml_xml constant varchar2(3 char) := 'xml'
-- c_email_eml
c_email_eml constant varchar2(3 char) := 'eml'
-- c_email_msg
c_email_msg constant varchar2(3 char) := 'msg'
-- c_zip_zip
c_zip_zip constant varchar2(3 char) := 'zip'
-- c_image_jpg
c_image_jpg constant varchar2(3 char) := 'jpg'
-- c_image_gif
c_image_gif constant varchar2(3 char) := 'gif'
-- c_image_png
c_image_png constant varchar2(3 char) := 'png'
-- c_image_bmp
c_image_bmp constant varchar2(3 char) := 'bmp'
-- c_onepagepdf_pdf
c_onepagepdf_pdf constant varchar2(10 char) := 'onepagepdf'
-- c_count_tags
c_count_tags constant varchar2(10 char) := 'count_tags'
-- c_get_attachments
c_get_attachments constant varchar2(16 char) := 'get_attachments'
-- c_xfa_form_fields
c_xfa_form_fields constant varchar2(16 char) := 'xfa_form_fields'
-- c_form_fields
c_form_fields constant varchar2(11 char) := 'form_fields'
-- c_defined_by_apex_item
c_defined_by_apex_item constant varchar2(9 char) := 'apex_item'
-- c_converter
c_converter constant varchar2(9 char) := 'converter'
-- c_aopreport
c_aopreport constant varchar2(9 char) := 'aopreport'
-- c_aoe
c_aoe constant varchar2(3 char) := 'aoe'
Output To
-- c_output_return
c_output_return constant varchar2(1 char) := null
-- c_output_browser
c_output_browser constant varchar2(7 char) := 'BROWSER'
-- c_output_procedure
c_output_procedure constant varchar2(9 char) := 'PROCEDURE'
-- c_output_procedure_browser
c_output_procedure_browser constant varchar2(17 char) := 'PROCEDURE_BROWSER'
-- c_output_procedure_inline
c_output_procedure_inline constant varchar2(17 char) := 'PROCEDURE_INLINE'
-- c_output_inline
c_output_inline constant varchar2(14 char) := 'BROWSER_INLINE'
-- c_output_directory
c_output_directory constant varchar2(9 char) := 'DIRECTORY'
-- c_output_db_directory
c_output_db_directory constant varchar2(12 char) := 'DB_DIRECTORY'
-- c_output_cloud
c_output_cloud constant varchar2(5 char) := 'CLOUD'
-- c_output_async
c_output_async constant varchar2(5 char) := 'ASYNC'
-- c_output_web_service
c_output_web_service constant varchar2(12 char) := 'WEB_SERVICE'
-- c_apex_office_edit
c_apex_office_edit constant varchar2(16 char) := 'APEX_OFFICE_EDIT'
-- c_pdf_region_pro
c_pdf_region_pro constant varchar2(14 char) := 'PDF_REGION_PRO'
Special
-- c_special_number_as_string
c_special_number_as_string constant varchar2(16 char) := 'NUMBER_TO_STRING'
-- c_special_report_as_label
c_special_report_as_label constant varchar2(16 char) := 'REPORT_AS_LABELS'
-- c_special_ir_filters_top
c_special_ir_filters_top constant varchar2(14 char) := 'FILTERS_ON_TOP'
-- c_special_ir_highlights_top
c_special_ir_highlights_top constant varchar2(17 char) := 'HIGHLIGHTS_ON_TOP'
-- c_special_ir_excel_header_f
c_special_ir_excel_header_f constant varchar2(18 char) := 'HEADER_WITH_FILTER'
-- c_special_ir_saved_report
c_special_ir_saved_report constant varchar2(19 char) := 'ALWAYS_REPORT_ALIAS'
-- c_special_ir_repeat_header
c_special_ir_repeat_header constant varchar2(13 char) := 'REPEAT_HEADER'
-- c_obfuscate_data
c_obfuscate_data constant varchar2(14 char) := 'OBFUSCATE_DATA'
Debug
-- c_debug_no
c_debug_no constant varchar2(3 char) := 'No'
-- c_debug_remote
c_debug_remote constant varchar2(3 char) := 'Yes'
-- c_debug_local
c_debug_local constant varchar2(5 char) := 'Local'
-- c_debug_application_item
c_debug_application_item constant varchar2(9 char) := 'APEX_ITEM'
Mode
-- c_mode_production
c_mode_production constant varchar2(15 char) := 'production'
-- c_mode_development
c_mode_development constant varchar2(15 char) := 'development'
Supported Languages; used for the translation of IR
-- c_en
c_en constant varchar2(5 char) := 'en'
-- c_nl
c_nl constant varchar2(5 char) := 'nl'
-- c_fr
c_fr constant varchar2(5 char) := 'fr'
-- c_de
c_de constant varchar2(5 char) := 'de'
Strings
-- c_init_null
c_init_null constant varchar2(5 char) := 'null;'
-- c_false
c_false constant varchar2(5 char) := 'false'
-- c_true
c_true constant varchar2(4 char) := 'true'
-- c_yes
c_yes constant varchar2(3 char) := 'Yes'
-- c_no
c_no constant varchar2(2 char) := 'No'
-- c_y
c_y constant varchar2(1 char) := 'Y'
-- c_n
c_n constant varchar2(1 char) := 'N'
Internal Use for conditional compilation - see wwv_flow_imp.sql
-- 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_182
c_apex_182 constant pls_integer := 20180928
-- c_apex_191
c_apex_191 constant pls_integer := 20190331
-- c_apex_192
c_apex_192 constant pls_integer := 20191004
-- c_apex_201
c_apex_201 constant pls_integer := 20200331
-- c_apex_202
c_apex_202 constant pls_integer := 20201001
-- c_apex_211
c_apex_211 constant pls_integer := 20210415
-- c_apex_212
c_apex_212 constant pls_integer := 20211015
-- c_apex_221
c_apex_221 constant pls_integer := 20220412
-- c_apex_222
c_apex_222 constant pls_integer := 20221007
-- c_apex_231
c_apex_231 constant pls_integer := 20230428
-- c_apex_232
c_apex_232 constant pls_integer := 20231031
-- c_apex_241
c_apex_241 constant pls_integer := 20240531
Variables
Others
-- g_async_status
g_async_status := apex_json.get_varchar2(p_path => 'status')
-- g_async_url
g_async_url := apex_json.get_varchar2(p_path => 'url')
Logger
-- g_logger_enabled : In case you use Logger (https://github.com/OraOpenSource/Logger), you can compile this package to enable Logger output: SQL> ALTER PACKAGE aop_api24_pkg COMPILE PLSQL_CCFLAGS = 'logger_on:TRUE'; When compiled and this global variable is set to true, debug will be written to logger too
g_logger_enabled boolean := true
Call to AOP
-- g_aop_url : AOP Server url
g_aop_url varchar2(200 char) := null
-- g_api_key : AOP API Key; only needed when AOP Cloud is used (http(s)://www.apexofficeprint.com/api)
g_api_key varchar2(50 char) := null
-- g_aop_mode : AOP Mode can be development or production; when running in development no cloud credits are used but a watermark is printed
g_aop_mode varchar2(15 char) := null
-- g_failover_aop_url : AOP Server url in case of failure of AOP url
g_failover_aop_url varchar2(200 char) := null
-- g_failover_procedure : When the failover url is used, the procedure specified in this variable will be called
g_failover_procedure varchar2(200 char) := null
-- g_template_type : 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_template_type varchar2(100 char) := null
-- g_output_converter : Set the converter to go to PDF (or other format different from template) e.g. officetopdf, libreoffice or libreoffice-standalone
g_output_converter varchar2(50 char) := null
-- g_output_locale : When using openofficeconverter, set the locale e.g. en, ne etc.
g_output_locale varchar2(50 char) := null
-- g_output_image_resolution : When using openofficeconverter, set the resolution of the image e.g. 300dpi, 600dpi, 900dpi or 1200dpi
g_output_image_resolution varchar2(50 char) := null
-- g_output_jpeg_compression : When using openofficeconverter, specify the JPEG compression, percentage between 0-100
g_output_jpeg_compression varchar2(50 char) := null
-- g_output_convert_to_pdfa : When using openofficeconverter, specify 1b or 2b which are standard PDF compliant versions, specifying any true value will convert to a PDF/A 1b compliant PDF.
g_output_convert_to_pdfa varchar2(50 char) := null
-- g_output_ua_compliant_pdf : When using openofficeconverter, specify true to create a PDF/UA compliant PDF
g_output_ua_compliant_pdf varchar2(50 char) := null
-- g_output_correct_page_nr : boolean to check for AOPMergePage text to replace it with the page number.
g_output_correct_page_nr boolean := false
-- g_output_lock_form : boolean that determines if the pdf forms should be locked/flattened.
g_output_lock_form boolean := false
-- g_lock_form_ignoring_sign : boolean that determines to lock/flatten everything in the output PDF but not the signature fields
g_lock_form_ignoring_sign boolean := false
-- g_sign_certificate_field : the name of the signature field to sign the output document (optional: invisible signature will be placed otherwise)
g_sign_certificate_field varchar2(100 char) := ''
-- g_identify_form_fields : 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_identify_form_fields boolean := false
-- g_proxy_override : null=proxy defined in the application attributes
g_proxy_override varchar2(300 char) := null
-- g_transfer_timeout : default of APEX is 180
g_transfer_timeout number(6) := 1800
-- g_wallet_path : null=defined in Manage Instance > Instance Settings
g_wallet_path varchar2(300 char) := null
-- g_wallet_pwd : null=defined in Manage Instance > Instance Settings
g_wallet_pwd varchar2(300 char) := null
-- g_https_host : The host name to be matched against the common name (CN) of the remote server's certificate for an HTTPS request.
g_https_host varchar2(300 char) := null
-- g_apex_web_service_rheader_n2 : Add a custome request header name when calling the AOP Server. This variable will set: apex_web_service.g_request_headers(2).name
g_apex_web_service_rheader_n2 varchar2(200 char) := null
-- g_apex_web_service_rheader_v2 : Add a custome request header value when calling the AOP Server. This variable will set: apex_web_service.g_request_headers(2).value
g_apex_web_service_rheader_v2 varchar2(4000 char) := null
-- g_output_filename : output
g_output_filename varchar2(300 char) := null
-- g_cloud_provider : dropbox, gdrive, onedrive, aws_s3, (s)ftp
g_cloud_provider varchar2(100 char) := null
-- g_cloud_location : directory in dropbox, gdrive, onedrive, aws_s3 (with bucket), (s)ftp
g_cloud_location varchar2(4000 char) := null
-- g_cloud_access_token : access token or credentials for dropbox, gdrive, onedrive, aws_s3, (s)ftp (needs json)
g_cloud_access_token varchar2(4000 char) := null
-- g_language : Language can be: en, fr, nl, de, used for the translation of filters applied etc. (translation build-in AOP)
g_language varchar2(2 char) := c_en
-- g_app_language : Language specified in the APEX app (primary language, translated language), when left to null, apex_util.get_session_lang is being used
g_app_language varchar2(20 char) := null
-- g_logging : ability to add your own logging: e.g. "request_id":"123", "request_app":"APEX", "request_user":"RND"
g_logging clob := ''
-- g_debug : set to 'Local' when only the JSON needs to be generated, 'Remote' for remote debug
g_debug varchar2(10 char) := null
-- g_debug_procedure : 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_debug_procedure varchar2(4000 char) := null
-- g_special : Special settings defined in the APEX Plug-in concerning Reports (colon separated), see p_special
g_special varchar2(4000 char) := null
-- g_app_id : APEX application id
g_app_id number := null
-- g_page_id : APEX page id
g_page_id number := null
-- g_user_name : APEX user name (APP_USER)
g_user_name varchar2(200 char) := null
-- g_force_create_apex_session : Force creating a new APEX session
g_force_create_apex_session boolean := false
-- g_caller_program : Either null or DA (dynamic action)
g_caller_program varchar2(2) := null
APEX Page Items
-- g_apex_items : 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"
g_apex_items varchar2(4000 char) := null
Layout for IR
-- g_rpt_header_font_name : Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163
g_rpt_header_font_name varchar2(50 char) := ''
-- g_rpt_header_font_size : 14
g_rpt_header_font_size varchar2(3 char) := ''
-- g_rpt_header_font_color : #071626
g_rpt_header_font_color varchar2(50 char) := ''
-- g_rpt_header_back_color : #FAFAFA
g_rpt_header_back_color varchar2(50 char) := ''
-- g_rpt_header_border_width : 1 ; '0' = no border
g_rpt_header_border_width varchar2(50 char) := ''
-- g_rpt_header_border_color : #000000
g_rpt_header_border_color varchar2(50 char) := ''
-- g_rpt_break_font_color : #071626
g_rpt_break_font_color varchar2(50 char) := ''
-- g_rpt_break_back_color : #FAFAFA
g_rpt_break_back_color varchar2(50 char) := ''
-- g_rpt_break_show_header : Show the header again after the break (Y/N)
g_rpt_break_show_header varchar2(1 char) := null
-- g_rpt_data_font_name : Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163
g_rpt_data_font_name varchar2(50 char) := ''
-- g_rpt_data_font_size : 14
g_rpt_data_font_size varchar2(3 char) := ''
-- g_rpt_data_font_color : #000000
g_rpt_data_font_color varchar2(50 char) := ''
-- g_rpt_data_back_color : #FFFFFF
g_rpt_data_back_color varchar2(50 char) := ''
-- g_rpt_data_border_width : 1 ; '0' = no border
g_rpt_data_border_width varchar2(50 char) := ''
-- g_rpt_data_border_color : #000000
g_rpt_data_border_color varchar2(50 char) := ''
-- g_rpt_data_alt_row_color : #FFFFFF for no alt row color, use same color as g_rpt_data_back_color
g_rpt_data_alt_row_color varchar2(50 char) := ''
-- g_rpt_group_border_color : default the same as data and header border color.
g_rpt_group_border_color varchar2(50 char) := ''
-- g_rpt_group_border_width : grouping border size, default 4.
g_rpt_group_border_width varchar2(50 char) := ''
-- g_rpt_header_vertical_align : possible values: top, center, bottom
g_rpt_header_vertical_align varchar2(50 char) := ''
-- g_rpt_data_vertical_align : possible values: top, center, bottom
g_rpt_data_vertical_align varchar2(50 char) := ''
Settings for Calendar
-- g_cal_type : can be month (default), week, day, list; constants can be used
g_cal_type varchar2(10 char) := c_cal_month
-- g_start_date : start date of calendar
g_start_date date := null
-- g_end_date : end date of calendar
g_end_date date := null
-- g_weekdays : translation for weekdays e.g. Monday:Tuesday:Wednesday etc.
g_weekdays varchar2(300 char) := null
-- g_months : translation for months e.g. January:February etc.
g_months varchar2(300 char) := null
-- g_color_days_sql : color the background of certain days. e.g. select 1 as "id", sysdate as "date", 'FF8800' as "color" from dual
g_color_days_sql varchar2(4000 char) := null
-- g_separate_pages : start calendar on new page (true) or start calendar on same page
g_separate_pages varchar2(5 char) := 'false'
-- g_alignment : align text on calender: left center or right
g_alignment varchar2(5 char) := 'right'
-- g_title_alignment : align title of the calendar: left right or center
g_title_alignment varchar2(5 char) := 'right'
-- g_day_alignment : align days of the calendar: left right or center
g_day_alignment varchar2(5 char) := 'right'
-- g_start_of_week : start of the week day: Monday (Mon) or Sunday (Sun)
g_start_of_week varchar2(3 char) := 'Mon'
-- g_new_row_per_event : show events vertically in new row (true). Show events horizontally in new column (false). Default is false.
g_new_row_per_event varchar2(5 char) := 'false'
Call to URL data source
-- g_url_http_method
g_url_http_method varchar2(10 char) := 'GET'
-- g_url_username
g_url_username varchar2(30 char) := null
-- g_url_password
g_url_password varchar2(300 char) := null
-- g_url_schema
g_url_schema varchar2(100 char) := 'Basic'
-- g_url_proxy_override
g_url_proxy_override varchar2(300 char) := 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 char) := null
-- g_url_wallet_pwd
g_url_wallet_pwd varchar2(300 char) := null
-- g_url_https_host : parameter for apex_web_service, not used, please apply APEX patch if issues
g_url_https_host varchar2(300 char) := null
-- g_url_credential_static_id
g_url_credential_static_id varchar2(300 char) := null
-- g_url_token_url
g_url_token_url varchar2(300 char) := null
Web Source Module (APEX >= 18.1)
-- g_web_source_first_row : parameter for apex_exec.open_web_source_query
g_web_source_first_row pls_integer := null
-- g_web_source_max_rows : parameter for apex_exec.open_web_source_query
g_web_source_max_rows pls_integer := null
-- g_web_source_total_row_cnt : parameter for apex_exec.open_web_source_query
g_web_source_total_row_cnt boolean := false
REST Enabled SQL (APEX >= 18.1)
-- g_rest_sql_auto_bind_items : parameter for apex_exec.open_remote_sql_query
g_rest_sql_auto_bind_items boolean := true
-- g_rest_sql_first_row : parameter for apex_exec.open_remote_sql_query
g_rest_sql_first_row pls_integer := null
-- g_rest_sql_max_rows : parameter for apex_exec.open_remote_sql_query
g_rest_sql_max_rows pls_integer := null
-- g_rest_sql_total_row_cnt : parameter for apex_exec.open_remote_sql_query
g_rest_sql_total_row_cnt boolean := false
-- g_rest_sql_total_row_limit : parameter for apex_exec.open_remote_sql_query
g_rest_sql_total_row_limit pls_integer := null
Input Data
-- g_replace_special_symbols : Option to replace special symbols in the selected columns/keys. Replaces +, -, *, /, and % by _.
g_replace_special_symbols varchar2(5 char) := null
-- g_override_html_expr_on_null : When HTML expressions are being used in reports, but they are null, they can be overwritten to use the report_null_values_as
g_override_html_expr_on_null boolean := false
IP Printer support
-- g_ip_printer_location
g_ip_printer_location varchar2(300 char) := null
-- g_ip_printer_version
g_ip_printer_version varchar2(300 char) := '1'
-- g_ip_printer_requester
g_ip_printer_requester varchar2(300 char) := coalesce(apex_application.g_user, USER)
-- g_ip_printer_job_name
g_ip_printer_job_name varchar2(300 char) := 'AOP'
-- g_ip_printer_return_output : null or 'Yes' or 'true'
g_ip_printer_return_output varchar2(5 char) := null
AOP Processing
-- g_pre_conversion_command : 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 varchar2(4000 char) := null
-- g_pre_conversion_command_p : Parameter (in JSON) before the conversion to another file format. These parameters should be present on aop_config.json file.
g_pre_conversion_command_p varchar2(4000 char) := null
-- g_post_conversion_command : 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 varchar2(4000 char) := null
-- g_post_conversion_command_p : Parameter (in JSON) after the conversion to another file format. These parameters should be present on aop_config.json file.
g_post_conversion_command_p varchar2(4000 char) := null
-- g_post_merge_command : The command to execute after the merge of files. This command should be present on aop_config.json file.
g_post_merge_command varchar2(4000 char) := null
-- g_post_merge_command_p : Parameter (in JSON) after the merge of files. These parameters should be present on aop_config.json file.
g_post_merge_command_p varchar2(4000 char) := null
-- g_pipeline_name : The name of the pipeline that will be executed.
g_pipeline_name varchar2(4000 char) := null
-- g_post_process_command : The command to execute. This command should be present on aop_config.json file.
g_post_process_command varchar2(4000 char) := null
-- g_post_process_command_p : Parameter (in JSON) in the post process command. These parameters should be present on aop_config.json file.
g_post_process_command_p varchar2(4000 char) := null
-- g_post_process_return_output : Either to return the output or not. Note this output is AOP's output and not the post process command output.
g_post_process_return_output boolean := true
-- g_post_process_delete_delay : AOP deletes the file provided to the command directly after executing it. This can be delayed with this option. Integer in milliseconds.
g_post_process_delete_delay number(9) := 1500
AOP Config
-- g_aop_config : AOP config file; anything here will overwrite or extend other attributes in the JSON. Make sure this is valid JSON.
g_aop_config varchar2(32767) := null
Convert characterset
-- g_convert : set to Y (c_y) if you want to convert the JSON that is send over; necessary for Arabic support
g_convert varchar2(1 char) := c_n
-- g_convert_source_charset : default of database
g_convert_source_charset varchar2(20 char) := null
-- g_convert_target_charset
g_convert_target_charset varchar2(20 char) := 'AL32UTF8'
-- g_stop_apex_engine : stop the APEX engine
g_stop_apex_engine varchar2(1 char) := c_n
-- g_run_with_dbms_scheduler : Run the call in the background through a sys.dbms_scheduler job, when finished call defined procedure.
g_run_with_dbms_scheduler varchar2(1 char) := c_n
Output
-- g_output_directory
g_output_directory varchar2(200 char) := '.'
-- g_output_sign_certificate : sign PDF with signature which is base64 encoded
g_output_sign_certificate varchar2(32000 char) := null
-- g_output_sign_certificate_pwd : sign PDF with password
g_output_sign_certificate_pwd varchar2(500 char) := null
-- g_output_sign_certificate_fld : sign PDF with the given signature field name
g_output_sign_certificate_fld varchar2(500 char) := null
-- g_output_sign_certificate_img : sign PDF with the given base64 encoded image as background for visible signature
g_output_sign_certificate_img varchar2(32767 char) := null
-- g_output_sign_certificate_txt : sign PDF with the given text to display on the signature
g_output_sign_certificate_txt varchar2(500 char) := null
-- g_output_sign_certificate_prp : sign PDF with the certificate privatekey password
g_output_sign_certificate_prp varchar2(32767 char) := null
-- g_output_split : split file: one file per page: true/false
g_output_split varchar2(5 char) := null
-- g_output_split_by_page : split file: one file per page: 1, 2, 3, ...
g_output_split_by_page number := null
-- g_output_split_by_string : split file: by string present on the page: e.g. "Invoice No" or "Invoice No || Invoice Number"
g_output_split_by_string varchar2(500 char) := null
-- g_output_merge : merge files into one PDF true/false
g_output_merge varchar2(5 char) := null
-- g_output_icon_font : the icon font to use for the output, Font-APEX or Font Awesome 5 (default)
g_output_icon_font varchar2(20 char) := null
-- g_output_even_page : PDF option to always print even pages (necessary for two-sided pages): true/false
g_output_even_page varchar2(5 char) := null
-- g_output_merge_making_even : PDF option to merge making all documents even paged (necessary for two-sided pages): true/false
g_output_merge_making_even varchar2(5 char) := null
-- g_output_page_margin : HTML to PDF option: margin in px, can also add top, bottom, left, right
g_output_page_margin varchar2(50 char) := null
-- g_output_page_orientation : HTML to PDF option: portrait (default) or landscape
g_output_page_orientation varchar2(10 char) := null
-- g_output_page_width : HTML to PDF option: width in px, mm, cm, in. No unit means px.
g_output_page_width varchar2(10 char) := null
-- g_output_page_height : HTML to PDF option: height in px, mm, cm, in. No unit means px.
g_output_page_height varchar2(10 char) := null
-- g_output_page_format : HTML to PDF option: a4 (default), letter
g_output_page_format varchar2(10 char) := null
-- g_output_page_number_start_at : Change the start of the page numbers in the template
g_output_page_number_start_at varchar2(10 char) := null
-- g_output_remove_last_page : PDF option to remove the last page; e.g. when the last page is empty
g_output_remove_last_page boolean := false
-- g_output_ignore_conv_errors : PDF option to ignore conversion errors during appending and prepending of files
g_output_ignore_conv_errors boolean := false
-- g_output_modified_date : Word/Powerpoint template option to set the modified date of the file. Must be in ISO format (Example: "2022-02-07T12:55:12") or in the date time format ("YYYY-MM-DD HH:mm:ss", "YYYY-MM-DD")
g_output_modified_date varchar2(50 char) := null
-- g_output_created_date : Word/Powerpoint template option to set the created date of the file. Must be in ISO format (Example: "2022-02-07T12:55:12") or in the date time format ("YYYY-MM-DD HH:mm:ss", "YYYY-MM-DD")
g_output_created_date varchar2(50 char) := null
-- g_output_compression : Compression algorithm: zip
g_output_compression varchar2(10 char) := null
-- g_output_compression_name : Name of the file after compression
g_output_compression_name varchar2(50 char) := null
-- g_output_form_fill_font : The name of the font when filling in a PDF Form, The font must be installed on the system, or provided as a file on assets folder or root of aop.
g_output_form_fill_font varchar2(250 char) := null
-- g_output_attachment_text : Specify the information string you want to place in the attachment retrieved from eml file (email), e.g. "Current Page: {attachmentCurrentPage} Total Pages: {attachmentTotalPage} Attachment Number : {attachmentIndex} of {attachmentFilename}"
g_output_attachment_text varchar2(250 char) := null
-- g_output_attachment_text_pos : The position of attachment text in the attachment retrieved from eml file (email), e.g. bottom-left, bottom-right, top-left, top-right or center
g_output_attachment_text_pos varchar2(20 char) := null
-- g_output_attachment_xml_json : While retrieving PDF attachments, if the attachment is an xml file, convert it to json.
g_output_attachment_xml_json boolean := false
-- g_output_insert_barcode : Insert a barcode in the PDF. This option should be provided as true while using {|barcode} tag on pdf template
g_output_insert_barcode boolean := false
Async call to AOP; a URL will be returned where the file can be polled from
-- g_async_status : Get the status of the async call (OK, error, false)
g_async_status varchar2(4000 char) := null
-- g_async_message : Get the status message of the async call
g_async_message varchar2(4000 char) := null
-- g_async_url : Get the URL where you can get the file when processing is complete
g_async_url varchar2(4000 char) := null
Call a Web Service where AOP will send the file to (POST Request)
-- g_web_service_url : URL to be called once AOP has created the document. AOP will do a POST request and headers can be specified
g_web_service_url varchar2(500 char) := null
-- g_web_service_headers : The headers for the POST request e.g. {"file_id": "F123", "access_token": "A456789"}
g_web_service_headers varchar2(4000 char) := null
Files
-- g_prepend_files_sql : format: select filename, mime_type, [file_blob, file_base64, url_call_from_db, url_call_from_aop, file_on_aop_server], [read_password] from my_table
g_prepend_files_sql clob := null
-- g_append_files_sql : format: select filename, mime_type, [file_blob, file_base64, url_call_from_db, url_call_from_aop, file_on_aop_server], [read_password] from my_table
g_append_files_sql clob := null
-- g_media_files_sql
g_media_files_sql clob := null
-- g_output_prepend_per_page : Prepend one or more pages before each page in the output. E.g. logo and company details before every document
g_output_prepend_per_page boolean := false
-- g_output_append_per_page : Append one or more pages after each page in the output. E.g. terms of conditions after every invoice
g_output_append_per_page boolean := false
Templates
-- g_template_start_delimiter : { is the default start delimiter used is a template, but you can set this variable with the following options: {, {{, <, <<
g_template_start_delimiter varchar2(2 char) := null
-- g_template_end_delimiter : } is the default end delimiter used in a template, but you can set this variable with the following options: }, }}, >, >>
g_template_end_delimiter varchar2(2 char) := null
-- g_cache_template : cache the template; an hash is returned in g_template_cache_hash
g_cache_template boolean := false
-- g_template_cache_hash : the hashed value of the cached version of the template on the AOP Server/Cloud
g_template_cache_hash varchar2(128 char) := null
-- g_use_template_when_no_cache : 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.
g_use_template_when_no_cache varchar2(1 char) := c_n
Sub-Templates
-- g_sub_templates_sql : format: select filename, mime_type, [file_blob, file_base64, url_call_from_db, url_call_from_aop, file_on_aop_server, name] from my_table
g_sub_templates_sql clob := null
Attachments
-- g_attachments_sql : format: select filename, mime_type, [file_blob, file_base64, url_call_from_db, url_call_from_aop, file_on_aop_server, name] from my_table
g_attachments_sql clob := null
Password protected PDF
-- g_output_read_password : protect PDF to read
g_output_read_password varchar2(200 char) := null
-- g_output_modify_password : protect PDF to write (modify)
g_output_modify_password varchar2(200 char) := null
-- g_output_pwd_protection_flag : 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_pwd_protection_flag number(4) := null
-- g_output_pdf_producer : PDF meta-data set producer data tag
g_output_pdf_producer varchar2(4000) := null
-- g_output_watermark : Watermark in PDF
g_output_watermark varchar2(4000) := null
-- g_output_watermark_color : Watermark option color
g_output_watermark_color varchar2(500) := null
-- g_output_watermark_font : Watermark option font
g_output_watermark_font varchar2(500) := null
-- g_output_watermark_size : Watermark option width
g_output_watermark_size varchar2(500) := null
-- g_output_watermark_opacity : Watermark option opacity
g_output_watermark_opacity varchar2(500) := null
-- g_output_watermark_rotation : Watermark option rotation
g_output_watermark_rotation varchar2(500) := null
-- g_output_copies : Requires output pdf, repeats the output pdf for the given number of times.
g_output_copies number := null
IG
-- g_ig_force_query : force the IG to use AOPs own implementation instead of apex_region.open_query_context
g_ig_force_query varchar2(1 char) := null
-- g_ig_use_alternative_label : force the IG to use the alternative label for the heading
g_ig_use_alternative_label varchar2(1 char) := null
JSON
-- g_anonymize_json : 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_anonymize_json varchar2(1 char) := c_n
-- g_use_data_export_pjson : instead of using the AOP specific code to generate the meta-data of reports, use apex_data_export.c_format_pjson
g_use_data_export_pjson varchar2(1 char) := c_n
CSV
-- g_output_text_delimiter : delimiter for tags
g_output_text_delimiter varchar2(200 char) := null
-- g_output_field_separator : field separator, default is ,
g_output_field_separator varchar2(200 char) := null
-- g_output_character_set : character set of CSV file
g_output_character_set varchar2(200 char) := null
Word
-- g_update_toc : Update the table of contents in Word document
g_update_toc boolean := false
DATA EXPORT - APEX 20.2 and higher
-- g_data_export_component_id
g_data_export_component_id number := null
-- g_data_export_view_mode
g_data_export_view_mode varchar2(100 char) := null
-- g_data_export_max_rows
g_data_export_max_rows number := null
-- g_data_export_file_name
g_data_export_file_name varchar2(255 char) := null
-- g_data_export_page_size
g_data_export_page_size apex_data_export.t_size := apex_data_export.c_size_letter
-- g_data_export_orientation
g_data_export_orientation apex_data_export.t_orientation := apex_data_export.c_orientation_portrait
-- g_data_export_data_only
g_data_export_data_only boolean := false
-- g_data_export_pdf_accessible
g_data_export_pdf_accessible boolean := false
OCI
-- g_oci_credential : Credentials used in sys.DBMS_CLOUD (Oracle Cloud Infrastructure credentials)
g_oci_credential varchar2(150 char) := null
-- g_oci_directory_name : Directory name used in sys.DBMS_CLOUD
g_oci_directory_name varchar2(150 char) := null
Inline Region
-- g_inline_region_static_id : Used when Output To is set to Inline Region
g_inline_region_static_id varchar2(150 char) := null
APEX Office Edit (AOE)
-- g_aoe_region_static_id : Used when Output To is set to c_apex_office_edit Specify here the Static ID of the APEX Office Edit Plug-in region
g_aoe_region_static_id varchar2(150 char) := null
-- g_aoe_primary_key_items : the primary key items defined in APEX Office Edit colon separated (will be automatically filled)
g_aoe_primary_key_items varchar2(4000 char) := null
-- g_aoe_primary_key_values : the primary key values of the records that where created by the procedure colon separated
g_aoe_primary_key_values varchar2(4000 char) := null
PDF Region Pro
-- g_pdf_region_static_id : Used when Output To is set to c_pdf_region_pro Specify here the Static ID of the PDF Region Pro Plug-in region
g_pdf_region_static_id varchar2(150 char) := null
-- g_pdf_primary_key_items : the primary key items defined in PDF Region Pro colon separated (will be automatically filled)
g_pdf_primary_key_items varchar2(4000 char) := null
-- g_pdf_primary_key_values : the primary key values of the records that where created by the procedure colon separated
g_pdf_primary_key_values varchar2(4000 char) := null
Others
-- g_is_component_used_yn : If you want to override the is_component_used_yn, you can specify 'Y' to always show or 'N' to never show.
g_is_component_used_yn varchar2(1 char) := null
-- g_visible_report_columns : 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_visible_report_columns varchar2(4000 char) := null
-- g_hidden_report_columns : 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
g_hidden_report_columns varchar2(4000 char) := null
AOP_DEBUG Procedure
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
| Name | Description |
|---|---|
p_message | The debug message. Occurrences of '%s' is replaced by p0 to p19. |
p_level | The log level for the message, default is |
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);
CLOB2BASE64 Function
convert a clob to base64, handy for HTML templates
Syntax
function clob2base64(
p_clob in clob)
return clob
Parameters
| Name | Description |
|---|---|
p_clob | Clob to be encoded to base64 |
| return | base64 encoded string |
BASE642CLOB Function
convert base64 to clob
Syntax
function base642clob(p_base64 in clob)
return clob
Parameters
| Name | Description |
|---|---|
p_base64 | Base64 to be decoded to clob |
| return | decoded string |
URL2BASE64 Function
convert a url with for example an image to base64
Syntax
function url2base64 (
p_url in varchar2)
return clob
Parameters
| Name | Description |
|---|---|
p_url | URL to be encoded to base64 |
| return | base64 encoded string |
GETMIMETYPE Function
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
| Name | Description |
|---|---|
p_file_ext | Enter the extension e.g. docx |
| return | Get the mime type e.g. application/vnd.openxmlformats-officedocument.wordprocessingml.document |
GETFILEEXTENSION Function
get the file extension of a mime type
Syntax
function getfileextension (
p_mime_type in varchar2)
return varchar2 deterministic
Parameters
| Name | Description |
|---|---|
p_mime_type | Enter the mime type e.g. application/vnd.openxmlformats-officedocument.wordprocessingml.document |
| return | returns the extension e.g. docx |
GETICON Function
get the Font Awesome / APEX icon of a mime type
Syntax
function geticon (
p_mime_type in varchar2)
return varchar2 deterministic
Parameters
| Name | Description |
|---|---|
p_mime_type | Enter the mime type e.g. application/vnd.openxmlformats-officedocument.wordprocessingml.document |
| return | returns the icon e.g. fa-word |
GET_BINDS Function
Copy of www_flow_utilities.get_binds
Syntax
function get_binds (
p_stmt in clob )
return sys.dbms_sql.varchar2_table
Parameters
| Name | Description |
|---|---|
p_stmt | SQL Statement |
BLOB2CLOB Function
BLOB to CLOB Conversion
Syntax
function blob2clob(p_blob in blob) return clob
Parameters
| Name | Description |
|---|---|
p_blob | Binary file |
| return | Character representation |
CLOB2BLOB Function
BLOB to CLOB Conversion
Syntax
function clob2blob(p_clob in clob) return blob
Parameters
| Name | Description |
|---|---|
p_blob | Binary file |
| return | Character representation |
BLOB2FILE Procedure
BLOB to FILE Conversion
Syntax
procedure blob2file(p_blob in blob,
p_directory in varchar2,
p_filename in varchar2)
Parameters
| Name | Description |
|---|---|
p_blob | Binary file |
p_directory | Database directory |
p_filename | Filename |
FILE2BLOB Function
FILE to BLOB Conversion
Syntax
function file2blob(p_directory in varchar2,
p_filename in varchar2)
return blob
Parameters
| Name | Description |
|---|---|
p_directory | Database directory |
p_filename | Filename |
IS_VALID_OUTPUT_TYPE Function
check template and output type compatibility
Syntax
function is_valid_output_type(p_template_type in varchar2,
p_output_type in varchar2)
return boolean
Parameters
| Name | Description |
|---|---|
p_template_type | mime type or file extension for the template |
p_output_type | mime type or file extension for the output |
| return | Description |
IS_VALID_TEMPLATE_HASH Function
check if the hash of the template cache is still valid and present on the AOP Server/Cloud
Syntax
function is_valid_template_hash(p_aop_url in varchar2 default g_aop_url,
p_hash in varchar2)
return boolean
Parameters
| Name | Description |
|---|---|
p_aop_url | URL of AOP Server or Cloud |
p_hash | hash of template |
| return | Description |
CREATE_APEX_SESSION Procedure
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
| Name | Description |
|---|---|
p_app_id | Description |
p_user_name | Description |
p_page_id | Description |
p_session_id | Description |
p_enable_debug | Description |
GET_APEX_SESSION Function
get an APEX session
Syntax
function get_apex_session
return apex_workspace_sessions.apex_session_id%type
Parameters
| Name | Description |
|---|---|
| return | Description |
JOIN_APEX_SESSION Procedure
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
| Name | Description |
|---|---|
p_session_id | Description |
p_app_id | Description |
p_page_id | Description |
p_enable_debug | Description |
DROP_APEX_SESSION Procedure
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
| Name | Description |
|---|---|
p_app_id | Description |
p_session_id | Description |
PLSQL_CALL_TO_AOP Function
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 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_attachments_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
Parameters
| Name | Description |
|---|---|
p_data_type | Define where the data is coming from. Following constants exists in aop_api_pkg: |
p_data_source | Depending the data type, define here the source: - |
p_template_type | Define where the template is stored. Following constants exists in aop_api_pkg: |
p_template_source | Depending the template_type, define here the filename, SQL statement, PL/SQL function or URL: - |
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: |
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: - |
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 (colon separated). Following constants can be used: |
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: - |
p_output_converter | Define the PDF converter you want to use. Multiple converters can be defined in the AOP Server. e.g. officetopdf, libreoffice, libreoffice-standalone |
p_aop_url | Description: URL where the AOP Server is running. For the AOP Cloud use |
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: |
p_output_split | Split PDF in multiple pages and create zip |
p_output_split_by_page | Split PDF in multiple number of pages specified and create a zip |
p_output_split_by_string | Split PDF in multiple pages by string present on the page and create a zip |
p_output_merge | Merge multiple files to one PDF |
| return | blob in defined output format containing result of merged template(s) with data and prepend and append files. |
Example
<code>
declare
l_binds wwv_flow_plugin_util.t_bind_list;
l_return blob;
l_output_filename varchar2(300) := 'output';
begin
-- set the output to JSON, so we see what is being sent to the AOP Server (uncomment next line)
-- aop_api_pkg.g_debug := 'Local';
-- set output to own custom debug table (uncomment next line)
-- aop_api_pkg.g_debug_procedure := 'aop_sample_pkg.custom_debug';
--
-- most minimalistic example
l_return := aop_api_pkg.plsql_call_to_aop (
p_data_type => aop_api_pkg.c_source_type_json,
p_data_source => '[{"hello":"world"}]',
p_template_type => aop_api_pkg.c_source_type_aop_template,
p_output_type => 'docx',
p_output_filename => l_output_filename,
p_aop_url => 'http://localhost:8010');
--
--
l_return := aop_api_pkg.plsql_call_to_aop (
p_data_type => aop_api_pkg.c_source_type_rpt,
p_data_source => 'report1',
p_template_type => null,
p_template_source => '',
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>', -- change the API key if you use the AOP Cloud
p_app_id => 498, -- change to APEX app id
p_page_id => 100); -- change to APEX page id
-- write output to table (uncomment next line)
-- insert into aop_output (output_blob, filename) values (l_return, l_output_filename);
end;