Simple favorites functionality for Apex applications

For my first ever blog post I choose "Add/Remove favorites" functionality for my apps with lots of pages and complex navigation.

I did some POC development and produced simple functionality that can be transformed into plugin.

Here is my solution.

Requirements:

• simple implementation
• low code
• to be reusable
• easy to maintain.

Solution

Create database objects for processing favorites

• sequence

CREATE SEQUENCE   "DEMO_FAVORITES_SEQ";

• table

CREATE TABLE DEMO_FAVORITES
(ID NUMBER,
APP_ID NUMBER NOT NULL,
PAGE_ID NUMBER NOT NULL,
APP_USER VARCHAR2(30),
CONSTRAINT DEMO_FAVORITES_PK PRIMARY KEY (ID) ENABLE);

• trigger

CREATE OR REPLACE TRIGGER DEMO_FAVORITES_BI
BEFORE INSERT ON DEMO_FAVORITES
FOR EACH ROW
BEGIN
IF :NEW.ID IS NULL THEN
:NEW.ID := DEMO_FAVORITES_SEQ.NEXTVAL;
END IF;
END;

• package

CREATE OR REPLACE PACKAGE DEMO_FAVORITES_PKG AS

   FUNCTION IS_FAVORITE (P_APP_ID  IN  NUMBER,

                         P_APP_PAGE_ID  IN  NUMBER,

                         P_APP_USER IN VARCHAR2)

      RETURN INT;

   PROCEDURE ADD_FAVORITE (P_APP_ID  IN  NUMBER,

                           P_APP_PAGE_ID  IN  NUMBER,

                           P_APP_USER IN VARCHAR2);

   PROCEDURE REMOVE_FAVORITE (P_APP_ID  IN  NUMBER,

                              P_APP_PAGE_ID  IN  NUMBER,

                              P_APP_USER IN VARCHAR2);

END DEMO_FAVORITES_PKG;

CREATE OR REPLACE PACKAGE BODY DEMO_FAVORITES_PKG AS

   FUNCTION IS_FAVORITE (P_APP_ID  IN  NUMBER,

                         P_APP_PAGE_ID  IN  NUMBER,

                         P_APP_USER IN VARCHAR2)

      RETURN INT

   IS

      L_EXISTS  INT;

   BEGIN

      SELECT COUNT(1)

        INTO L_EXISTS

        FROM DUAL

       WHERE EXISTS (SELECT NULL

                       FROM DEMO_FAVORITES

                      WHERE APP_ID = P_APP_ID

                        AND APP_PAGE_ID = P_APP_PAGE_ID

                        AND UPPER (APP_USER) = UPPER(P_APP_USER));

      RETURN L_EXISTS;

   END;

   PROCEDURE ADD_FAVORITE (P_APP_ID  IN  NUMBER,

                           P_APP_PAGE_ID  IN  NUMBER,

                           P_APP_USER IN VARCHAR2)

   IS

   BEGIN

      INSERT INTO DEMO_FAVORITES

         (APP_ID, APP_PAGE_ID, APP_USER)

      VALUES

         (P_APP_ID, P_APP_PAGE_ID, P_APP_USER);

   END;

   PROCEDURE REMOVE_FAVORITE (P_APP_ID  IN  NUMBER,

                              P_APP_PAGE_ID  IN  NUMBER,

                              P_APP_USER IN VARCHAR2)

   IS

   BEGIN

      DELETE DEMO_FAVORITES

       WHERE APP_ID = P_APP_ID

         AND APP_PAGE_ID = P_APP_PAGE_ID

         AND UPPER(APP_USER) = UPPER(P_APP_USER);

   END;

END DEMO_FAVORITES_PKG;

Application 

• On page 0 create page button FAVORITES
• Name: FAVORITES
• Button template: Icon
• Template Options: Remove UI Decorations
• Icon: fa-star
• Static ID: favorites

• Create Dynamic Actions on button FAVORITES
• Name: Is favorite (on start only)
• Event Scope: Once
• Server-side Condition:
• Type: PL/SQL Expression
• PL/SQL Code: 
  DEMO_FAVORITES_PKG.IS_FAVORITE (:APP_ID, :APP_PAGE_ID, :APP_USER) = 1
• True
• Type: Execute JavaScript Code
• JavaScript Code:
  var element = document.getElementById("favorites");
  element.classList.add("t-Button--hot");
  element.value = "1"
• Fire on Initialization: Yes

• Create Dynamic Actions on button FAVORITES
• Name: Is not favorite (on start only)
• Event Scope: Once
• Server-side Condition:
• Type: PL/SQL Expression
• PL/SQL Code: 
  DEMO_FAVORITES_PKG.IS_FAVORITE (:APP_ID, :APP_PAGE_ID, :APP_USER) = 0
• True
• Type: Execute JavaScript Code
• JavaScript Code:
  var element = document.getElementById("favorites");
  element.classList.remove("t-Button--hot");
  element.value = "0";
• Fire on Initialization: Yes

• Create Dynamic Actions on button FAVORITES
• Name: Add to favorites
• Client-side Condition:
• Type: JavaScript expression
• JavaScript Code:
  this.triggeringElement.value == "0";
• True
• Type: Execute PL/SQL Code
• PL/SQL Code: DEMO_FAVORITES_PKG.ADD_FAVORITE (:APP_ID, :APP_PAGE_ID, :APP_USER);COMMIT;
• Type: Execute JavaScript Code
• JavaScript Code:var element = document.getElementById("favorites");
  element.classList.add("t-Button--hot");element.value = "1";

• Create Dynamic Actions on button FAVORITES
• Name: Remove from favorites
• Client-side Condition:
• Type: JavaScript expression
• JavaScript Code:
  this.triggeringElement.value == "1";
• True
• Type: Execute PL/SQL Code
• PL/SQL Code: DEMO_FAVORITES_PKG.REMOVE_FAVORITE (:APP_ID, :APP_PAGE_ID, :APP_USER);COMMIT;
• Type: Execute JavaScript Code
• JavaScript Code:var element = document.getElementById("favorites");
  element.classList.remove("t-Button--hot");element.value = "0";

Demo:

https://apex.oracle.com/pls/apex/f?p=99752:1