Thursday 3 February 2011

APEX Object Synonyms

It's been almost a month since my last post - it's been January and I'm going to leave it at that.

I thought I'd continue 2011 with a little snippet I wrote to save some of my frustrations.

Occasionally I find myself reading the package spec for an Oracle Apex package, and I'd like to know the synonym name I can refer to it with - or I'm aware of a synonym name and I'd like to pull up the package specification for a look-see.

Either way, I always find myself trying to remember what to replace with what, seeing now it's usually just WWV_FLOW <-> APEX, but sometimes it's APEX <-> HTMLDB, and sometimes it's something else...

So I wrote a little query to help myself do the mapping between synonym name and package name, maybe you'll take it on board:
SELECT owner, synonym_name, table_name
FROM   dba_synonyms
WHERE  synonym_name LIKE 'APEX/_%' ESCAPE '/'
AND    owner LIKE 'APEX/_%' ESCAPE '/'
ORDER BY synonym_name
/

OWNER        SYNONYM_NAME                   TABLE_NAME
------------ ------------------------------ ------------------------------
APEX_030200  APEX_ACTIVITY_LOG              WWV_FLOW_USER_ACTIVITY_LOG
APEX_030200  APEX_APPLICATION               WWV_FLOW
APEX_030200  APEX_APPLICATION_FILES         WWV_FLOW_FILES
APEX_030200  APEX_APPLICATION_GLOBAL        WWV_FLOW_GLOBAL
APEX_030200  APEX_COLLECTION                WWV_FLOW_COLLECTION
APEX_030200  APEX_COLLECTIONS               WWV_FLOW_COLLECTIONS
APEX_030200  APEX_CUSTOM_AUTH               HTMLDB_CUSTOM_AUTH
APEX_030200  APEX_INSTANCE_ADMIN            WWV_FLOW_INSTANCE_ADMIN
APEX_030200  APEX_ITEM                      HTMLDB_ITEM
APEX_030200  APEX_LANG                      HTMLDB_LANG
APEX_030200  APEX_LDAP                      WWV_FLOW_LDAP
APEX_030200  APEX_LOGIN                     HTMLDB_LOGIN
APEX_030200  APEX_MAIL                      WWV_FLOW_MAIL
APEX_030200  APEX_MAIL_ATTACHMENTS          WWV_FLOW_USER_MAIL_ATTACHMENTS
APEX_030200  APEX_MAIL_LOG                  WWV_FLOW_USER_MAIL_LOG
APEX_030200  APEX_MAIL_QUEUE                WWV_FLOW_USER_MAIL_QUEUE
APEX_030200  APEX_PLSQL_JOB                 WWV_FLOW_PLSQL_JOB
APEX_030200  APEX_PLSQL_JOBS                WWV_FLOW_PLSQL_JOBS
APEX_030200  APEX_SITE_ADMIN_PRIVS          HTMLDB_SITE_ADMIN_PRIVS
APEX_030200  APEX_UI_DEFAULT                WWV_FLOW_HINT
APEX_030200  APEX_USER_ACCESS_LOG           WWV_FLOW_USER_ACCESS_LOG
APEX_030200  APEX_UTIL                      HTMLDB_UTIL

22 rows selected.

Yes, unfortunately at the moment I'm doing most of my work in Apex3.x - I feel so dated!

No comments: