Oracle APEX Process Plug-in
Overview
The supporting process plug-in enables a developer to handle the plug-in session state and to pernamently delete images uplaoded using RESTful service and removed from the current rich text HTML.
The plug-in exposes the following actions:
- Clear CLOB
- Load CLOB
- Update CLOB
- Delete Removed Images
Clear CLOB
The action clears the item plug-in collection data and sets item's session state to NULL. Additionally, other page items can be specified to be cleared using APEX PL/SQL API APEX_UTIL.SET_SESSION_STATE.
The action can be used both on pre-rendering (Before Header, After Header, and Before Regions) and after page submission (Processing and After Processing).
- Attributes
| Attribute | Type | Required | Description |
|---|---|---|---|
| Item | Page item | Yes | An item implementing the plug-in. |
| Other Page Item(s) | Page item(s) | No | Other page items to be cleared using Oracle APEX API. |
Load CLOB
Table
The action loads CLOB contents into the plug-in session state based on the given table specification.
- Attributes
- Example
| Attribute | Type | Required | Description |
|---|---|---|---|
| Item | Page item | Yes | Item name implementing the plug-in |
| Schema | Text | Yes | Schema that owns the table |
| Table Name | Text | Yes | The case-sensitive table or view name. |
| CLOB Column Name | Text | Yes | The column name which stores the CLOB content |
| PK(s) Column(s) Name(s) | Text | Yes | Comma separated list of primary key columns needed to fetch the CLOB |
| Item(s) Containing PK(s) Value(s) | Page Item(s) | Yes | Comma separated list of APEX page items containing primary keys values |
The screenshot below shows an example implementation.
SQL Query
The action loads CLOB content into the plug-in session state based on the given SQL Query returning CLOB value.
- Attributes
- Example
| Attribute | Type | Required | Description |
|---|---|---|---|
| Item | Page item | Yes | An item implementing the plug-in. |
| SQL Query | SQL Query | Yes | Valid SQL Query returning CLOB content. |
The SQL query returning CLOB below.
select CLOB_COLUMN from UC_RTE_CLOBS where id = :P4_ID;
Function Body Returning CLOB
The action loads CLOB content into the plug-in session state based on the given PL/SQL Code.
- Attributes
- Example
| Attribute | Type | Required | Description |
|---|---|---|---|
| Item | Page item | Yes | Item implementing the plug-in |
| Function Body returning CLOB | PL/SQL Code | Yes | Valid anonymous PL/SQL code with return statement. |
declare
v_clob clob;
v_result clob;
v_test number;
begin
if :P4_ID is null then
v_result := to_clob(null);
else
select
CLOB_CONTENT
into
v_result
from
UC_RTE_CLOBS
where
id = :P4_ID
;
end if;
return v_result;
end;
Update CLOB
Table
The action updates the plug-in CLOB value in the database based on given table specification.
- Attributes
- Example
| Attribute | Type | Required | Description |
|---|---|---|---|
| Item | Page item | Yes | Item name implementing the plug-in |
| Schema | Text | Yes | Schema that owns the table |
| Table Name | Text | Yes | The case-sensitive table or view name. |
| CLOB Column Name | Text | Yes | The column name which stores the CLOB content |
| PK(s) Column(s) Name(s) | Text | Yes | Comma separated list of primary key columns needed to fetch the CLOB |
| Item(s) Containing PK(s) Value(s) | Page Item(s) | Yes | Comma separated list of APEX page items containing primary keys values |
The screenshot below shows an example implementation.
PL/SQL Code
The action updates the plug-in CLOB value in the database based on a developer custom PL/SQL code. The provided code can reference bind variable :CONTENT referencing the current rich text HTML CLOB value after submitting a page.
- Attributes
- Example
| Attribute | Type | Required | Description |
|---|---|---|---|
| Item | Page item | Yes | An item implementing the plug-in |
| PL/SQL Code | PL/SQL Code | Yes | A valid anonymous PL/SQL code with return statement. |
See an example PL/SQL code below.
begin
update
#OWNER#.UC_RTE_CLOBS
set
CLOB_CONTENT = :CONTENT
where
ID = :P4_ID
;
end;
Image handling
Delete Removed Images
The action allows a developer to define custom PL/SQL code executed in the loop over removed images from rich text HTML. Images included in the loop are the images uploaded using the RESTful service. The provided PL/SQL code can reference an image meta-data stored in the plug-in collection using the plug-in package variables listed below.
- Attributes
- Example
- Plug-in PL/SQL variables
| Attribute | Type | Required | Description |
|---|---|---|---|
| Item | Page item | Yes | Item name implementing the plug-in |
| PL/SQL Code | PL/SQL Code | Yes | The PL/SQL code deleting removed images |
See an example PL/SQL code below.
declare
v_removed_img_id number := UC_FROALA_RTE.g_froala_image_id;
begin
delete from UC_FROALA_SAMPLE_BLOBS where id = v_removed_img_id;
end;
Learn more when image can be safely removed in the database in About \ Concepts \ Images.
You can learn more about the plug-in session state in About \ Concepts \ Session State
| Variable | Type | Description |
|---|---|---|
UC_FROALA_RTE.g_froala_image_id | VARCHAR2(4000) | an image ID assigned after a successful image upload |
UC_FROALA_RTE.g_froala_image_get_url | VARCHAR2(4000) | an image URL |
UC_FROALA_RTE.g_froala_image_safe_to_delete | VARCHAR2(1) | an image flag Y/N indicating if the image can be safely removed |
Process Uploaded Images
The action allows a developer to define custom PL/SQL code executed in the loop over recently uploaded images using the plug-in RESTful service. The provided PL/SQL code can reference an image meta-data stored in the plug-in collection using the plug-in package variables listed below.
- Attributes
- Example
- Plug-in PL/SQL variables
| Attribute | Type | Required | Description |
|---|---|---|---|
| Item | Page item | Yes | Item name implementing the plug-in |
| PL/SQL Code | PL/SQL Code | Yes | The PL/SQL code processing uploaded images |
See an example PL/SQL code below.
declare
v_clob_id constant number := :P1432_ID;
v_img_exists number;
v_img_id number;
v_img_url varchar2(4000);
begin
v_img_id := UC_FROALA_RTE.g_froala_image_id;
v_img_url := UC_FROALA_RTE.g_froala_image_get_url;
select
count(1)
into
v_img_exists
from
UC_FROALA_SAMPLE_CLOB_BLOBS
where
CLOB_ID = v_clob_id
and BLOB_ID = v_img_id
;
if v_img_exists = 0 then
insert into
UC_FROALA_SAMPLE_CLOB_BLOBS(
CLOB_ID,
BLOB_ID,
IMAGE_URL
) values(
v_clob_id,
v_img_id,
v_img_url
);
end if;
end;
| Variable | Type | Description |
|---|---|---|
UC_FROALA_RTE.g_froala_image_id | VARCHAR2(4000) | an image ID assigned after a successful image upload |
UC_FROALA_RTE.g_froala_image_get_url | VARCHAR2(4000) | an image URL |
UC_FROALA_RTE.g_froala_image_onload | VARCHAR2(1) | an image flag Y/N indicating if the image was available before the plug-in was initialized. |