Skip to main content

Custom attributes

Application

The APEX Office Edit plug-in application attributes can be set via Shared Components \ Component Settings \ UC - APEX Office Edit (AOE).

Application component settings are applied to all instances of APEX Office Edit in an application. The component settings AOE Server URL and URL to RESTful Service module can be overridden per AOE instance using the region attribute Settings \ Override Component Settings.

API Key

TypeRequiredDependent on
TextNoNone

Enter a valid APEX Office Edit API key. The API key can be obtained on the APEX Office Edit portal: https://www.apexofficeedit.com/ords/uc/r/aoe_portal/

Default Document Filename

TypeRequiredDependent on
TextYesNone

Specify the document's default name. The default filename is used when AOE creates a new file.

The attribute supports APEX substitution variables (e.g., &APP_AOE_DEFAULT_FILENAME.) which can be used to dynamically set the filename from the session state.

AOE Server URL

TypeRequiredDependent on
TextYesNone

Specify the URL where the AOE server component is running.

The default value is https://api.apexofficeedit.com/

URL to RESTful Service module

AOE has a default ORDS RESTful service module named APEX Office Edit. The URL must be copied from SQL Workshop \ RESTful Services \ Module Definition, for example, http://www.united-codes.com/ords/hr/aoe/.

It is strongly recommended to use the unmodified RESTful service that is delivered with AOE. A valid URL to the ORDS RESTful service module should refer to a RESTful service module with the following handlers:

URI TemplateMethodOperation
files/:fileidGETget document information
files/:fileidPOSTsave document as, rename a document
files/:fileid/contentsGETget document content
files/:fileid/contentsPOSTsave changes
files/create/:typeGETcreate a new document
files/knockknockGETcheck if RESTful service is available
files/meta/GETget document meta-data after loading a document

Component

All the plug-in region attributes available in the Oracle APEX page designer are listed and described below.

Allowed File Types

TypeRequiredDependent on
CheckboxYesNone

Use the following document types to restrict the kinds of documents that the end-user can create and open.

Available options:

  • Microsoft Word
  • Microsoft Excel
  • Microsoft PowerPoint
  • Open Document Text
  • Open Document Spreadsheet
  • Open Document Presentation
  • Open Document Draw

Settings

TypeRequiredDependent on
CheckboxNoNone

Use the following options to set the plug-in configuration.

Disable Creating Files

CheckedNot Checked
Creating a new file is disabledCreating a new file is enabled

Disable Editor Messages

CheckedNot Checked
Only the editor's error messages are shown. All success messages are ignored and not shown to the end-user.all messages including success messages are displayed within the editor and the end-user has to close the prompts manually. For example, when a document has been successfully updated, the editor shows the message Successfully uploaded file.

Disable Exporting

The editor supports exporting a currently opened document to different types - for example, Microsoft Word documents can be exported to PDF, RTF, DOC, and ePUB.

This option prohibits the end-user from exporting a document using the editor's toolbar.

CheckedNot Checked
Exporting enabledExporting disabled

Disable Printing

Printing should be only enabled when an APEX application using AOE is in the same origin as the AOE host server files. Otherwise, an attempt to print will raise cross origin error.

CheckedNot Checked
The editor print button is not displayedThe editor print button is displayed only when the APEX application and AOE server files are in the same origin

Disable Renaming

CheckedNot Checked
Renaming a document using the editor filename input in the toolbar is disabled.Renaming a document using the editor filename input in the toolbar is enabled.

Disable Save As

CheckedNot Checked
Saving a currently opened document as a new file is disabledSaving a currently opened document as a new file is disabled

Override Component Settings

When selected, the AOE application components settings AOE Server URL and URL to RESTful Service module can be overridden on the region level in the page designer. Otherwise, the attributes defined in the plug-in component settings are used by default.

AOE Server URL (attribute)

TypeRequiredDependent on
TextYesSettings \ Override Component Settings is checked

When provided at the page designer level, it overrides the Custom attributes \ Application \ AOE Server-Side URL value. See Custom attributes \ Application \ AOE Server-Side URL for more information.

URL to RESTful Service module (attribute)

TypeRequiredDependent on
TextYesSettings \ Override Component Settings is checked

When provided on the page designer level, it overrides the Custom attributes \ Application \ URL to RESTful Service module value. See Custom attributes \ Application \ URL to RESTful Service module for more information.

Source

The attribute defines how the AOE maintains documents. Depending on the attribute value, there are different implementations.

Default Table

When selected, AOE maintains documents using the AOE_DEFAULT_FILES table.

The default table implementation supports built-in document versioning using the AOE_DEFAULT_VERSIONS table. Versioning must be enabled using the PL/SQL variable AOE_PLUGIN.g_default_versioning in the Initialization PL/SQL Code attribute.

Important

The table must be accessible by the AOE RESTful Service module.

Custom Table

When selected, AOE maintains documents using a table specification provided by a developer via the AOE region attributes. A developer has to provide a table specification, including:

  • Table name
  • Primary key(s) column(s)
  • Column name for a document BLOB content
  • Column name for a document filename
  • Column name for a document MIME Type
  • Column name for a document's last modification time
  • Column name for a document's current version (optional)
  • Column name for a document owner

Important: the table must be accessible by the plug-in RESTful Service module.

Custom PL/SQL

When selected, AOE maintains documents using PL/SQL code provided by a developer. A developer has to create a package containing the required procedures for handling documents operations, such as:

  • AOE_PLUGIN.g_doc_getfileinfo - fetching a document meta-data
  • AOE_PLUGIN.g_doc_create- saving (creating) a new file based on an empty template BLOB of the requested type
  • AOE_PLUGIN.g_doc_update- updating a document
  • AOE_PLUGIN.g_doc_saveas- saving a document as a new file
  • AOE_PLUGIN.g_doc_rename- renaming an existing document

The package name has to be entered into the Custom PL/SQL Package Name attribute, and procedure names must be provided using variables in the Initialization PL/SQL Code attribute. Learn more in Custom Attributes \ Initialization PL/SQL Code

Important

The package must be accessible by the AOE RESTful Service module.

Table Name

TypeRequiredDependent on
TextYesSource \ Custom Table

A custom table name where documents are stored. The table name can be prefixed with #OWNER# to reference the current parsing schema, ie. #OWNER#.AOE_DEFAULT_FILES.

A custom table must have (at least) the following types of columns:

DescriptionType
Column storing a document's primary keyVARCHAR2 | NUMBER
Column storing a document's filenameVARCHAR2
Column storing a document's owner (an application end-user username)VARCHAR2
Column storing a document's MIME-typeVARCHAR2
Column storing a document's current versionNUMBER
Column storing a document's contentBLOB
Column storing a document's last modification timeTIMESTAMP(6)
Multiple Primary Keys
A custom table can use multiple primary keys to identify a document. Comma-delimited column names must be set using the Primary Key(s) Column(s) attribute.

Primary Key(s) Column(s)

TypeRequiredDependent on
TextYesSource \ Custom Table

Specify a comma-delimited list of column names used as primary keys for a document in the custom table.

Item(s) Containing Primary Key(s) Value(s)

TypeRequiredDependent on
Page Item(s)YesNone

A given APEX page item(s) is (are) used to identify a document to load from the database table. When any APEX item specified as the primary key is set to NULL, the AOE Editor shows the document creation panel or read-only panel, depending on the plug-in attributes.

Content Column

TypeRequiredDependent on
TextYesSource \ Custom Table

A column name whose type is BLOB and stores a document's file contents.

Filename Column

TypeRequiredDependent on
TextYesSource \ Custom Table

A column name whose type is VARCHAR2 and stores a document's filename (including the file extension).

MIME Type Column

TypeRequiredDependent on
TextYesSource \ Custom Table

A column name whose type is VARCHAR2 and stores a document's file MIME type.

Last Update Column

TypeRequiredDependent on
TextYesSource \ Custom Table

A column name whose type is TIMESTAMP(6) and stores a document's last modification time.

Version Column

TypeRequiredDependent on
TextNoSource \ Custom Table

A column name whose type is VARCHAR2 and stores a document's current version.

Owner Column

TypeRequiredDependent on
TextYesSource \ Custom Table

A column name whose type is VARCHAR2 and stores a document's owner name (APEX user name).

Document Permissions

TypeRequiredDependent on
Select listYesNone

Available options include:

  • Everyone can read and edit
  • Only the author can read and edit
  • Everyone can read, but only the author can edit
  • Custom function returning document permissions

Everyone can read and edit

AOE allows all users to read and edit all requested files.

Only the author can read and edit

A document author is the APEX end-user username stored in the column OWNER.

Everyone can read, but only the author can edit

AOE allows all users to preview any file, but only the author can modify a document.

Custom function returning document permissions

The plug-in uses a custom PL/SQL function defined as the plug-in variable g_doc_func_file_perm in Initialization PL/SQL Code attribute to distinguish the current end-user permission to the currently requested document.

Custom PL/SQL Package Name

TypeRequiredDependent on
TextNoNone

This package executes the additional procedures and functions defined in the Initialization PL/SQL Code attribute.

Important

The package must be accessible by the AOE RESTful Service module.

Initialization PL/SQL Code

TypeRequiredDependent on
PL/SQL CodeNoNone

The attribute sets additional configuration options using the variables described below. The provided PL/SQL code is evaluated when the AOE Editor is rendered.

Important

The given functions and procedures names must be declared in the package specified in the Custom PL/SQL Package Name.

Default procedures and functions

To minimalize the need to define Initialization PL/SQL Code variables for every AOE Editor instance, a developer can specify only the package name using the Custom PL/SQL Package Name attribute. The only condition is that an undefined variable is reflected in the default procedure or function in the given package.

See the variable mapping to default procedures/functions in the table below.

VariableDefault procedure name
g_doc_func_file_permaoe_fn_file_perm
g_doc_func_get_file_idaoe_fn_file_get_new_id
g_doc_callback_createaoe_callback_create
g_doc_callback_updateaoe_callback_update
g_doc_callback_readaoe_callback_read
g_doc_getfileinfoaoe_proc_getfileinfo
g_doc_createaoe_proc_create
g_doc_updateaoe_proc_update
g_doc_saveasaoe_proc_saveas
g_doc_renameaoe_proc_rename
g_doc_func_get_blankrest_create_blank

For example, if AOE is using a Source set to Custom PL/SQL, the attribute Custom PL/SQL Package Name is set to CUSTOM_PKG, and the package uses the default procedures aoe_proc_getfileinfo, aoe_proc_create, aoe_proc_update, aoe_proc_saveas, aoe_proc_rename then a developer doesn't have to specify custom procedures using variables g_doc_getfileinfo, g_doc_create, g_doc_update, g_doc_saveas, g_doc_rename, and g_doc_func_get_blank.

The AOE RESTful Service module will try to execute a custom procedure/function defined in Initialization PL/SQL Code. If the variable is not specified, AOE will try running the default procedure/function. If the default procedure/function is not defined, AOE will raise a PL/SQL error. If the default and custom procedure/function are specified, AOE will use the custom procedure/function.

g_default_versioning

TypeUsed whenExample
BooleanSource = Default TableAOE_PLUGIN.g_default_versioning := true;
AOE_PLUGIN.g_default_versioning := true;

When set to true

Saving a document results in:

  • updating a document in the table AOE_DEFAULT_FILES
  • creating a new version in the table AOE_DEFAULT_VERSIONS

When set to false or when not defined

Saving a document results in updating a document in the table AOE_DEFAULT_FILES, but document versioning in the table AOE_DEFAULT_VERSIONS is ignored.

g_doc_func_file_perm

TypeUsed whenExample
VARCHAR2(30)Document Permissions = Custom function returning document permissionsAOE_PLUGIN.g_doc_func_file_perm := 'aoe_fn_file_perm';

Provide a function name returning the end-user permissions to the currently requested document.

IMPORTANT

The given function must:

  • be declared in the package provided via Custom PL/SQL Package Name attribute
  • accept the specified arguments (described below)
  • return a VARCHAR2 string containing the end-user permissions to the requested document

The function specification is the following:

function function_name(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_file_id in varchar2,
p_user_id in varchar2
) return VARCHAR2;
ParameterTypeDescription
p_application_idNUMBERAn application ID in which the AOE is running
p_page_idNUMBERAn application page ID in which AOE is running
p_session_idNUMBERAn application session ID in which AOE is running
p_file_idVARCHAR2A document ID. When AOE uses a custom table and multiple primary keys, the value contains a comma-separated list of primary key values.
p_user_idVARCHAR2An APEX application end-user username

Document permissions pattern
The returned value from the function must follow the pattern C:L:U:S:R:P:E where:

  • C is permission to create a document
  • L is permission to load a document,
  • U is permission to update a document,
  • S is permission to save a document as a new file,
  • R is permission to rename a document,
  • P is permission to print a document,
  • E is permission to export a document

Allowed values are 0 and 1, where:

  • 0 is permission denied,
  • 1 is permission granted.

g_doc_func_get_file_id

TypeUsed whenExample
VARCHAR2(30)Source = Custom TableAOE_PLUGIN.g_doc_func_get_file_id := 'aoe_fn_file_get_new_id';

Provide a function that returns a document's primary key(s) value(s) when the end-user creates a new file. Use this function only when primary key(s) value(s) can't be obtained using a trigger or other database methods. The function should return a VARCHAR2 value containing new document primary key(s) value(s) separated with a comma.

Important

The given function must:

  • be declared in the package provided via the Custom PL/SQL Package Name attribute
  • accept the defined arguments
  • return a VARCHAR2 string containing the new document primary key(s) value(s) delimited with a comma

The function specification is the following:

function aoe_fn_file_get_new_id(
p_session_id in number,
p_application_id in number,
p_page_id in number,
p_user_id in varchar2,
p_pks_column_names in varchar2,
p_pks_item_values in varchar2
) return varchar2;

The function parameters are:

ParameterTypeDescription
p_application_idNUMBERAn application ID in which the AOE is running
p_page_idNUMBERAn application page ID in which AOE is running
p_session_idNUMBERAn application session ID in which AOE is running
p_file_idVARCHAR2A document ID. When AOE uses a custom table and multiple primary keys, the value contains a comma-separated list of primary key values
p_user_idVARCHAR2An APEX application end-user username
p_pks_column_namesVARCHAR2The current value of the Primary Key(s) Column(s) attribute
p_pks_item_valuesVARCHAR2The current value of the Item(s) Containing Primary Key(s) Value(s) attribute
Example

When AOE uses a custom table with two primary keys, ID and ID2, the function should return the value X,Y where:

  • X is a new value for column ID
  • Y is a new value for column ID2
...
function example_function(
p_session_id in number,
p_application_id in number,
p_page_id in number,
p_user_id in varchar2,
p_pks_column_names in varchar2,
p_pks_item_values in varchar2
) return varchar2
is
v_result varchar2(4000);
begin
v_result := 'X,Y';

return v_result;
end;
...

g_doc_getfileinfo

TypeUsed whenExample
VARCHAR2(30)Source = Custom PL/SQL CodeAOE_PLUGIN.g_doc_getfileinfo := 'aoe_custom_getfileinfo';

Provide a procedure name that fetches information about the currently requested document.

The given procedure must:

  • be declared in the package provided via the Custom PL/SQL Package Name attribute
  • accept the defined arguments
  • return OUT parameters describing the requested document

The procedure specification is:

procedure aoe_custom_getfileinfo(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_file_id in varchar2,
p_out_content out blob,
p_out_filename out varchar2,
p_out_mime_type out varchar2,
p_out_last_update_date out timestamp,
p_out_version out varchar2,
p_out_blob_owner out varchar2
);

The procedure parameters are:

ParameterTypeNULL ableDescription
p_application_idNUMBERNoAn application ID in which AOE is running
p_page_idNUMBERNoAn application page ID in which AOE is running
p_session_idNUMBERNoAn application session ID in which AOE is running
p_file_idVARCHAR2NoA document ID. When AOE uses a custom table and multiple primary keys, the value contains a comma-separated list of primary keys values
p_user_idVARCHAR2NoAn APEX application end-user username
p_out_contentBLOBNoThe requested document contents.
p_out_filenameVARCHAR2NoThe requested document filename.
p_out_mime_typeVARCHAR2NoThe requested document MIME-TYPE
p_out_last_update_dateTIMESTAMPNoThe requested document's last modification as a TIMESTAMP
p_out_versionVARCHAR2YesThe requested document's current version. The current document version is not required to fetch the document information successfully.
p_out_blob_ownerVARCHAR2NoThe requested document owner.

g_doc_create

TypeUsed whenExample
VARCHAR2(30)Source = Custom PL/SQL CodeAOE_PLUGIN.g_doc_create := 'aoe_custom_create';

Provide a procedure name for saving a new document of the requested type based on an empty BLOB template.

The given procedure must:

  • be declared in the package provided via the Custom PL/SQL Package Name attribute
  • accept the defined arguments
  • return OUT parameters

The procedure specification is the following:

procedure aoe_custom_create(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_file_id out varchar2,
p_file_content in blob,
p_file_filename in out varchar2,
p_file_mime_type in varchar2,
p_file_last_update_date in out timestamp,
p_file_version in out number,
p_file_blob_owner in out varchar2
);

The procedure parameters are:

ParameterNULL ableDescription
p_application_idNoAn application ID in which AOE is running
p_page_idNoAn application page ID in which AOE is running
p_session_idNoAn application session ID in which AOE is running
p_user_idNoAn APEX application end-user username
p_file_idNoThe procedure has to set the OUT value to identify a newly created document.
p_file_contentNoThe procedure receives an empty BLOB template of the requested type.
p_file_filenameNoThe procedure receives a default filename defined in the plug-in application component settings and a file extension of the given type. The procedure can modify the received filename, and it has to set it as the OUT parameter value.
p_file_mime_typeNoThe procedure receives a requested document MIME-TYPE.
p_file_last_update_dateNoThe procedure receives a SYSTIMESTAMP, and sets the OUT parameter value with the current document modification time as a TIMESTAMP.
p_file_versionYesThe requested document's current version. The current document's version is not required to handle a document successfully.
p_file_blob_ownerNoThe requested document owner that will be used while evaluating document permissions.

g_doc_update

TypeUsed whenExample
VARCHAR2(30)Source = Custom PL/SQL CodeAOE_PLUGIN.g_doc_update := 'aoe_custom_update';

Provide a procedure name to update a document.

The given procedure must:

  • be declared in the package provided via the Custom PL/SQL Package Name attribute
  • accept the defined arguments
  • return OUT parameters

The procedure specification is:

procedure aoe_custom_update(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_file_id in varchar2,
p_file_content in blob,
p_file_filename in varchar2,
p_file_mime_type in varchar2,
p_file_last_update_date in out timestamp,
p_file_version in out number,
p_file_blob_owner in varchar2
);

The procedure parameters are:

ParameterNULL ableDescription
p_application_idNoAn application ID in which AOE is running
p_page_idNoAn application page ID in which tAOE is running
p_session_idNoAn application session ID in which AOE is running
p_user_idNoAn APEX application end-user username
p_file_idNoA procedure has to set the OUT value identifying a newly created document.
p_file_contentNoA procedure receives an empty BLOB template of the requested type.
p_file_filenameNoA procedure receives the current document filename.
p_file_mime_typeNoA procedure receives the current document MIME-TYPE.
p_file_last_update_dateNoA procedure receives the last document modification time and sets the OUT parameter value with the current modification time as a TIMESTAMP.
p_file_versionYesA requested document's current version. The current document version is not required to handle a document successfully.
p_file_blob_ownerNoThe current document owner.

g_doc_saveas

TypeUsed whenExample
VARCHAR2(30)Source = Custom PL/SQL CodeAOE_PLUGIN.g_doc_saveas := 'aoe_custom_saveas';

Provide a procedure name that saves a document as a new file.

The given procedure must:

  • be declared in the package provided via the Custom PL/SQL Package Name attribute
  • accept the defined arguments
  • return OUT parameters

The procedure specification is the following:

procedure aoe_custom_saveas(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_file_id in out varchar2,
p_file_content in blob,
p_file_filename in out varchar2,
p_file_mime_type in varchar2,
p_file_last_update_date in out timestamp,
p_file_version in out number,
p_file_blob_owner in varchar2
);

The procedure parameters are:

ParameterNULL ableDescription
p_application_idNoAn application ID in which AOE is running
p_page_idNoAn application page ID in which AOE is running
p_session_idNoAn application session ID in which AOE is running
p_user_idNoAn APEX application end-user username
p_file_idNoThe procedure receives the current document ID to be saved as a new file and sets the OUT value identifying a newly created document.
p_file_contentNoThe procedure receives the current document BLOB to be saved as a new file.
p_file_filenameNoThe procedure receives a new filename and sets the OUT value if the procedure logic changes the document filename.
p_file_mime_typeNoThe procedure receives the current document MIME-TYPE.
p_file_last_update_dateNoThe procedure receives the current document modification time and sets the OUT value identifying a new document's current modification time as a TIMESTAMP.
p_file_versionYesThe requested document's current version. The current document's version is not required to handle a document successfully.
p_file_blob_ownerNoThe current document owner.

g_doc_rename

TypeUsed whenExample
VARCHAR2(30)Source = Custom PL/SQL CodeAOE_PLUGIN.g_doc_rename := 'aoe_custom_rename';

Provide a procedure name that renames a document using the provided filename in the AOE editor as input.

The given procedure must:

  • be declared in the package provided via the Custom PL/SQL Package Name attribute
  • accept the defined arguments
  • return OUT parameters

The procedure specification is the following:

procedure aoe_custom_rename(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_file_id in varchar2,
p_file_mime_type in varchar2,
p_file_filename in out varchar2,
p_file_last_update_date in out timestamp,
p_file_version in out number
);

The procedure parameters are:

ParameterNULL ableDescription
p_application_idNoAn application ID in which AOE is running
p_page_idNoAn application page ID in which AOE is running
p_session_idNoAn application session ID in which AOE is running
p_user_idNoAn APEX application end-user username
p_file_idNoThe procedure receives the current document ID to be renamed.
p_file_contentNoThe procedure receives the current document BLOB.
p_file_filenameNoThe procedure receives a new filename and sets the OUT value if the procedure logic changes the document filename.
p_file_mime_typeNoThe procedure receives the current document MIME-TYPE.
p_file_last_update_dateNoThe procedure receives a document's last modification time and sets the OUT parameter value with the current modification time as a TIMESTAMP.
p_file_versionYesThe requested document's current version. The current document version is not required to handle a document successfully.
p_file_blob_ownerNoThe current document owner.

g_doc_func_get_blank

This global variable allows you to use your custom function to get the file that will be opened while creating a first blank file. This could be used for cases where you might need a document with different layout structure, size, margin than default whenever creating a new file.

TypeUsed whenRequiredExample
VARCHAR2(30)Source = Default Table | Custom Table | Custom PL/SQLNoAOE_PLUGIN.g_doc_func_get_blank := 'aoe_file_custom_blank';
The custom function must be defined inside Custom Package.

The given procedure must:

  • be declared in the package provided via Custom PL/SQL Package Name attribute
  • accept the defined arguments

If not provided, AOE will create a blank template with default layout and margin.

procedure aoe_file_custom_blank(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_doc_mimetype in varchar2
);

The procedure parameters are:

ParameterNULL ableDescription
p_application_idNoAn application ID in which AOE is running
p_page_idNoAn application page ID in which AOE is running
p_session_idNoAn application session ID in which AOE is running
p_user_idNoAn APEX application end-user username
p_doc_mimetypeNoMime type of the document being loaded.

Example of function for the blank template in apex_collection per session. Please view the Demo of Custom Blank templates in sample App for more info.

function aoe_file_custom_blank(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_doc_mimetype in varchar2
) return blob is
v_result blob;
v_log_scope varchar2(30) := 'aoe_file_custom_blank';
begin
aoe_rest.log_info(v_log_scope, 'start');
aoe_rest.log_info(v_log_scope, '... parameters:');

aoe_rest.log_info(v_log_scope, '...... @p_application_id: %s' , NVL(to_char(p_application_id), '%NULL%'));
aoe_rest.log_info(v_log_scope, '...... @p_page_id: %s' , NVL(to_char(p_page_id), '%NULL%'));
aoe_rest.log_info(v_log_scope, '...... @p_session_id: %s' , NVL(to_char(p_session_id), '%NULL%'));
aoe_rest.log_info(v_log_scope, '...... @p_user_id: %s' , NVL(to_char(p_user_id), '%NULL%'));
aoe_rest.log_info(v_log_scope, '...... @p_doc_mimetype: %s' , NVL(p_doc_mimetype, '%NULL%'));

aoe_rest.log_info(v_log_scope, '... attach APEX session');
apex_session.attach(
p_app_id => p_application_id,
p_page_id => p_page_id,
p_session_id => p_session_id
);
select
blob001
into
v_result
from
apex_collections
where
collection_name = 'P360_CUSTOM_BLANK_FILES'
and c002 = p_doc_mimetype;

aoe_rest.log_info(v_log_scope, 'end (return blob)');
return v_result;
end aoe_file_custom_blank;

Another example that gets the template from web service.

function GET_NEW_TEMPLATE(p_application_id in NUMBER,
p_page_id in NUMBER,
p_session_id in NUMBER,
p_user_id in VARCHAR2,
p_doc_mimetype in VARCHAR2
) return BLOB
as
l_extension VARCHAR2(10);
l_blob_to_return BLOB;
begin
-- MIME type to extension.
l_extension := AOE_REST.get_file_extension(p_doc_mimetype);

-- Add a custom if-else block or modify the below code to set the starter file for a respective extension.
CASE l_extension
WHEN 'docx' THEN l_blob_to_return := APEX_WEB_SERVICE.MAKE_REST_REQUEST_B ( p_url => 'https://www.apexofficeprint.com/docs/assets/files/normal_substitution-6041070b40d09b1264e623ecaeb01ac3.docx', p_http_method => 'GET');
WHEN 'xlsx' THEN l_blob_to_return := APEX_WEB_SERVICE.MAKE_REST_REQUEST_B ( p_url => 'https://www.apexofficeprint.com/docs/assets/files/normal_substitution-1c6d4e5bdf4b704060c8554c6501fcec.xlsx', p_http_method => 'GET');
-- For other extension types, get a blank file (default starter file).
ELSE l_blob_to_return := APEX_WEB_SERVICE.CLOBBASE642BLOB(AOE_REST.get_blank_template( p_type => l_extension));

END CASE;
return l_blob_to_return;
end GET_NEW_TEMPLATE;

g_doc_callback_create

TypeUsed whenRequiredExample
VARCHAR2(30)Source = Default Table | Custom Table | Custom PL/SQLNoAOE_PLUGIN.g_doc_callback_read := 'aoe_callback_create';

Provide a procedure name to be executed whenever the end-user successfully creates a document.

If not provided, AOE does not execute the callback.

The given procedure must:

  • be declared in the package provided via Custom PL/SQL Package Name attribute
  • accept the defined arguments

The procedure specification is the following:

procedure aoe_callback_create(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_file_id in varchar2,
p_file_content in blob,
p_file_filename in varchar2,
p_file_mime_type in varchar2,
p_file_last_update_date in timestamp,
p_file_version in number,
p_file_blob_owner in varchar2
);

The procedure parameters are:

ParameterNULL ableDescription
p_application_idNoAn application ID in which AOE is running
p_page_idNoAn application page ID in which AOE is running
p_session_idNoAn application session ID in which AOE is running
p_user_idNoAn APEX application end-user username
p_file_idNoThe procedure receives the current document ID to be renamed.
p_file_contentNoThe procedure receives the current document BLOB which is an empty template.
p_file_filenameNoThe procedure receives a new filename and sets the OUT value if the procedure logic changes the filename.
p_file_mime_typeNoThe procedure receives the current document MIME-TYPE.
p_file_last_update_dateNoThe procedure receives a document's last modification time and sets the OUT value with the current modification time as a TIMESTAMP.
p_file_versionYesThe requested document's current version.
p_file_blob_ownerNoThe current document owner.

g_doc_callback_read

TypeUsed whenRequiredExample
VARCHAR2(30)Source = Default Table | Custom Table | Custom PL/SQLNoAOE_PLUGIN.g_doc_callback_read := 'aoe_callback_read';

Provide a procedure name to be executed a document's contents are read. If not provided, AOE does not perform the callback.

The given procedure must be:

  • declared in the package provided via Custom PL/SQL Package Name attribute
  • accepting the defined arguments

The procedure specification is the following:

procedure aoe_callback_read(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_file_id in varchar2,
p_file_content in blob,
p_file_filename in varchar2,
p_file_mime_type in varchar2,
p_file_last_update_date in timestamp,
p_file_version in number,
p_file_blob_owner in varchar2
);

The procedure parameters are:

ParameterNULL ableDescription
p_application_idNoAn application ID in which AOE is running
p_page_idNoAn application page ID in which AOE is running
p_session_idNoAn application session ID in which AOE is running
p_user_idNoAn APEX application end-user username
p_file_idNoThe procedure receives the current document ID to be renamed.
p_file_contentNoThe procedure receives the requested document contents as BLOB.
p_file_filenameNoThe procedure receives a new filename and sets the OUT value if the procedure logic changes the filename.
p_file_mime_typeNoThe procedure receives the current document MIME-TYPE.
p_file_last_update_dateNoThe procedure receives a document's last modification time and sets the OUT value with the current modification time as TIMESTAMP.
p_file_versionYesThe requested document's current version.
p_file_blob_ownerNoThe current document owner.

g_doc_callback_update

TypeUsed whenRequiredExample
VARCHAR2(30)Source = Default Table | Custom Table | Custom PL/SQLAOE_PLUGIN.g_doc_callback_read := 'aoe_callback_update';

Provide a procedure name to be executed when a document's contents are updated. If not provided, AOE does not perform the callback.

The given procedure must be:

  • declared in the package provided via Custom PL/SQL Package Name attribute
  • accepting the defined arguments

The procedure specification is the following:

procedure aoe_callback_update(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_file_id in varchar2,
p_file_content in BLOB,
p_file_filename in varchar2,
p_file_mime_type in varchar2,
p_file_last_update_date in timestamp,
p_file_version in number,
p_file_blob_owner in varchar2
);

The procedure parameters are:

ParameterNULL ableDescription
p_application_idNoAn application ID in which the AOE is running
p_page_idNoAn application page ID in which AOE is running
p_session_idNoAn application session ID in which AOE is running
p_user_idNoAn APEX application end-user username
p_file_idNoThe procedure receives the current document ID to be renamed.
p_file_contentNoThe procedure receives the modified document as BLOB.
p_file_filenameNoThe procedure receives a new filename and sets the OUT value if the procedure logic changes the filename.
p_file_mime_typeNoThe procedure receives the current document MIME-TYPE.
p_file_last_update_dateNoThe procedure receives a document's last modification time and sets the OUT value with the current modification time as a TIMESTAMP.
p_file_versionYesThe requested document's current version.
p_file_blob_ownerNoThe current document owner.

g_doc_callback_rename

TypeUsed whenRequiredExample
VARCHAR2(30)Source = Default Table | Custom Table | Custom PL/SQLAOE_PLUGIN.g_doc_callback_read := 'aoe_callback_rename';

Provide a procedure name to be executed when a document is renamed. If not provided, AOE does not perform the callback.

The given procedure must:

  • be declared in the package provided via Custom PL/SQL Package Name attribute
  • accept the defined arguments

The procedure specification is the following:

procedure aoe_callback_rename(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_file_id in varchar2,
p_file_content in BLOB,
p_file_filename_old in varchar2,
p_file_filename_new in varchar2,
p_file_mime_type in varchar2,
p_file_last_update_date in timestamp,
p_file_version in number,
p_file_blob_owner in varchar2
);

The procedure parameters are:

ParameterNULL ableDescription
p_application_idNoAn application ID in which the AOE is running
p_page_idNoAn application page ID in which AOE is running
p_session_idNoAn application session ID in which AOE is running
p_user_idNoAn APEX application end-user username
p_file_idNoThe procedure receives the current document ID to be renamed.
p_file_contentNoThe procedure receives the current document BLOB.
p_file_filename_oldNoThe procedure receives an old filename before renaming a document.
p_file_filename_newNoThe procedure receives a new filename after renaming a document.
p_file_mime_typeNoThe procedure receives the document MIME-TYPE.
p_file_last_update_dateNoThe procedure receives the document's last modification time and sets the OUT value with the current modification time as a TIMESTAMP.
p_file_versionYesThe requested document's current version.
p_file_blob_ownerNoThe current document owner.

g_doc_callback_saveas

TypeUsed whenRequiredExample
VARCHAR2(30)Source = Default Table | Custom Table Custom PL/SQLAOE_PLUGIN.g_doc_callback_read := 'aoe_callback_saveas';

Provide a procedure name to be executed when a document is saved as a new file. If not provided, AOE does not perform the callback.

The given procedure must:

  • be declared in the package provided via Custom PL/SQL Package Name attribute
  • accept the defined arguments

The procedure specification is the following:

procedure aoe_callback_saveas(
p_application_id in number,
p_page_id in number,
p_session_id in number,
p_user_id in varchar2,
p_file_id_old in varchar2,
p_file_id_new in varchar2,
p_file_content in BLOB,
p_file_filename in varchar2,
p_file_mime_type in varchar2,
p_file_last_update_date in timestamp,
p_file_version in number,
p_file_blob_owner in varchar2
);

The procedure parameters are:

ParameterNULL ableDescription
p_application_idNoAn application ID in which AOE is running
p_page_idNoAn application page ID in which AOE is running
p_session_idNoAn application session ID in which AOE is running
p_user_idNoAn APEX application end-user username
p_file_id_oldNoThe procedure receives a document ID before saving it as a new file.
p_file_id_newNoThe procedure receives a document ID after saving it as a new file.
p_file_contentNoThe procedure receives the current document BLOB.
p_file_filenameNoThe procedure receives a new filename and sets the OUT value if the procedure logic changes the filename.
p_file_mime_typeNoThe procedure receives the current document MIME-TYPE.
p_file_last_update_dateNoThe procedure receives a document's last modification time and sets the OUT value with the current modification time as a TIMESTAMP.
p_file_versionYesThe requested document's current version.
p_file_blob_ownerNoThe current document owner.