Hacks

These are all "hacks" that I have spent countless of hours on realizing because of the abundant and often contradictory information found on the internet (stackxchange, etc.), but also erroneous solutions generated by, e.g., ChatGPT.

Next Up

• Authentication
  • Custom Auth for own user database
  • OpenID (Keycloak) for authentication
  • Sources to start:
    • https://www.youtube.com/watch?v=TgsHxDT0VXw
    • https://www.youtube.com/watch?v=7C8dUZeKsE0
    • https://forums.oracle.com/ords/apexds/post/how-to-log-in-with-user-credentials-from-database-table-6626
    • https://www.google.com/search?client=firefox-b-d&q=oracle+apex+user+authentication+by+table

Report Pages

Interactive Reports (IR)

Conditional Edit Column

Use Case

Current user shall be able to edit report rows based on condition(s).

Example

• Context: The example is based on an IR sourced from a regular table, the editing takes place via a modal dialog.
• Edit Condition: OPEN_ID column contains same user name as current user name.
• Style: Edit column with pencil style icon to edit rows where authorized in a modal dialog¹.

The built-in link attribute of a page body doesn't do the job, because with its condition statements, the whole link column is either turned on or off, no can do for individual rows. That's why it has to be done with a combination of SQL and a helper column that will contain the conditional edit buttons.

Report Page Body

The SQL in the report page body selects the existing table data and creates the helper column EDIT_LINK. The column is empty where the condition is not met, it contains the URL (via apex_page.get_url()²) and the icon (fa-edit) where the condition is met.

select *,
       CASE lower(OPEN_ID)
         when lower(:APP_USER) THEN
            '<a href="'||apex_page.get_url(p_page => '<modal dialog page id>', p_items => 'p<modal dialog page id>_id', p_values => ID)||'"><span class="fa fa-edit"></span></a>'
        ELSE NULL
    END EDIT_LINK
  from <table name>

Helper Column EDIT_LINK

The column text is EDIT_LINK Column settings:

• Link type: Link
• Heading:  
• Link
  • Target: Page <x>
  • Link Text: &EDIT_LINK. (see ³ and ⁴)

Modal Dialog: Set Value Upon Dialog Closure

This is a topic I found especially hard to deal with: I want to set a creation date in a table when the entry is actually created. Doing this with a table setting of column and default value, it somehow produced an empty cell. So I found the following solution:

1. Go to modal page
2. Create a process in category Processing named, e.g., Set Value of type Execute Code. It should be put after the standard modal form proceses Process Form.
3. Enter the following PL/SQL Code:

   :<modal dialog var> := <desired value>;
   

   I wanted to have a creation time, so, for modal dialog page 3 (P3_CREATED_ON refers to CREATED_ON on the source page and from there to the source table column of the same name):

   :P3_CREATED_ON := sysdate;
   

   If you want to execute SQL:⁵

   update <table name>
   set <column name> = <desired value>
   where <PK id variable name>= :<modal dialog ID variable name>;
   

Suppress Message After Modal Dialog Create

The message can be styled (I didn't figure out how to suppress it completely) via a theme JS API.⁶ The code simply needs to be put into the events of the modal dialog page's source page, unter Page Load, as a Dynamic Action the Action Type of which should be Execute JavaScript Code.

Sources

Footnotes

1. https://forums.oracle.com/ords/apexds/post/how-to-open-modal-dialog-page-from-hyperlink-6555 ↩

2. https://docs.oracle.com/en/database/oracle/apex/22.1/aeapi/GET_URL-Function.html ↩

3. https://forums.oracle.com/ords/apexds/post/how-to-make-link-text-conditional-6681 ↩

4. https://docs.oracle.com/en/database/oracle/apex/24.1/htmdb/about-using-substitution-strings.html#GUID-C47D5D9B-3476-48EA-A0B1-DBA96E6E283B ↩

5. https://forums.oracle.com/ords/apexds/post/how-can-i-perform-an-sql-statement-insert-data-into-table-u-4869 ↩

6. https://apex.oracle.com/pls/apex/apex_pm/r/ut/javascript-apis ↩