Query to retrieve all Oracle APEX application IDs and workspaces

1
votes

Is there a means of querying a database environment to retrieve all Oracle APEX application IDs, together with the workspace name they belong to?

I basically want to create an LOV within an Oracle APEX app that can see all APEX application IDs and Workspaces.

I tried the APEX_APPLICATIONS view but that only displays info within the workspace you are in. I want to see all workspaces and application ids.

1
sqloracleoracle-apex

1 Answers

2
votes

Is granting the apex_administrator_role to the parsing schema plausible? Your query would probably still want to exclude internal workspaces such as INTERNAL, COM.ORACLE.CUST.REPOSITORY and COM.ORACLE.APEX.REPOSITORY

SQL> conn hr/hr@XE1
Connected.
SQL> 
SQL> 
SQL> select count(1) 
  2  from apex_applications;

  COUNT(1)
----------
         0

SQL> disconnect

Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> connect sys/oracle@XE1 as sysdba
Connected.
SQL> grant apex_administrator_role to hr;

Grant succeeded.

SQL> commit;

Commit complete.

SQL> conn hr/hr@XE1
Connected.
SQL> select count(1)
  2  from apex_applications
  3  ;

  COUNT(1)
----------
        60

note: This will also give your schema access to the APEX_INSTANCE_ADMIN API.