Process

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).

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.

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.

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.

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.

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.

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.

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;

:::tip Learn more

Learn more when image can be safely removed in the database in About / Concepts / Images.

:::

:::tip learn more

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.

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.