T24_FIND_APEX_ID

CREATE OR REPLACE FUNCTION "T24_FIND_APEX_ID"
(   
    P_APEX IN VARCHAR2,
    P_ID IN NUMBER
)
RETURN VARCHAR2
IS
   V_COUNT NUMBER;
   V_SQL VARCHAR2(4000);
   V_ITEMS NUMBER;
BEGIN

   -- ITEMS
   V_ITEMS := 0;

   -- SEARCH ALL ALL_TAB_COLS IN APEX SCHEMA
   FOR C_TAB IN ( SELECT TABLE_NAME, COLUMN_NAME FROM ALL_TAB_COLS WHERE OWNER = 'APEX_'||P_APEX AND COLUMN_NAME LIKE '%%' AND DATA_TYPE = 'NUMBER')
   LOOP
      -- SQL TEMPLATE
      V_SQL := q'[SELECT COUNT(*) FROM APEX_$APEX$.$TABLE$ WHERE $COLUMN$ = '$ID$']';
      -- PARSE SQL
      V_SQL := REPLACE( V_SQL, '$APEX$', P_APEX );
      V_SQL := REPLACE( V_SQL, '$TABLE$', C_TAB.TABLE_NAME );
      V_SQL := REPLACE( V_SQL, '$COLUMN$', C_TAB.COLUMN_NAME );
      V_SQL := REPLACE( V_SQL, '$ID$', P_ID );
      -- EXECUTE
      EXECUTE IMMEDIATE V_SQL INTO V_COUNT;
      -- ITEMS
      V_ITEMS := V_ITEMS + 1;
      -- CHECK
      IF V_COUNT > 0 THEN
         RETURN 'SEARCHED ITEMS='||V_ITEMS||', FOUND='||C_TAB.TABLE_NAME||'.'||C_TAB.COLUMN_NAME;
      END IF;
   END LOOP;

   -- RETURN NOT FOUND
   RETURN 'SEARCHED ITEMS='||V_ITEMS||', NOT FOUND';

END;