13.7.5.3 Using Custom SQL Involving Remote Data

Set a region or LOV's Local Post-Processing to SQL Query to query remote data from the #APEX$SOURCE_DATA# inline view.

At runtime, the APEX engine substitutes #APEX$SOURCE_DATA# for the entire JSON_TABLE() query it normally uses to process remote REST data. The SQL Query property that appears will initially contain a statement that produces the same results as before:

select EMPNO,
       ENAME,
       HIREDATE
  from #APEX$SOURCE_DATA#

But you can edit this SQL statement to add additional WHERE clauses, JOIN clauses, additional SELECT list columns or expressions, or really anything that produces a resulting valid SELECT statement.

For example, to join the EMPNO column value with local tables EMP_TRAINING and EMP_COURSE to produce a list of courses for retrieved employees having a status of completed or certified, you could write the query:

select r.EMPNO,
       r.ENAME,
       r.HIREDATE,
       c.NAME as COURSE_NAME,
       t.STATUS
  from #APEX$SOURCE_DATA# r
  join EMP_TRAINING t 
    on r.EMPNO = t.EMPNO
  join EMP_COURSE c
    on t.COURSE_ID = c.ID
 where t.STATUS in ('COMPLETED','CERTIFIED')

There are no strict requirements beyond the statement's:

• Including a reference to #APEX$SOURCE_DATA#
• Being syntactically correct SQL

This means your Local Post-Processing SQL statement can also compute aggregates, rename columns, or simply do whatever your use case requires. If you need to dynamically construct your SQL statement, you can use the PL/SQL Function Body Returning SQL Query type instead.