Wrong number or types of arguments to what?!

Have you ever received the following error?

SQL> select x.abc from dual x;
select x.abc from dual x
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'OGC_X'

If you have, the reason is the planets aligned in the Oracle world just to cause confusion.

• you're pre 11g
• you've mis-typed a column
• you're using a simple alias

Essentially, prior to 11g, there are two synonyms defined on the database who's name share some commonly used aliases.

SQL> select synonym_name, table_owner, table_name from all_synonyms where synonym_name in ('X','Y');

SYNONYM_NAME         TABLE_OWNER          TABLE_NAME
-------------------- -------------------- --------------------
X                    MDSYS                OGC_X
Y                    MDSYS                OGC_Y

So if you've mis-typed a column, Oracle tries to work out what your identifier is mapping to and if it finds some random match, it will report a seemingly random error.

These days the public synonyms are more appropriately called OGC_X and OGC_Y, but that doesn't mean you might not have any functions or synonyms defined in your own database that might also be used as table aliases. Heck, when I wrote this post I confused myself again because I had a dummy function in my database called XY.

So there are ways to circumvent things like this happening

1. Use a standard aliases for your tables - this seems pedantic, but it's worthwhile. They're not hard to conjure - first three letters (organisations org), first letter of each word (resource_types rt) - but keep it consistent. This will make life easier for developers that need to read SQL, and even Oracle likes you to use aliases for performance reasons.
   At one site, our code didn't pass muster if our table aliases didn't match the prescribed list.
2. Make your functions/synonyms descriptive - nobody wants to find a function called X or ABC and have to chase up what it does. Use some standard naming conventions.
3. Don't make mistakes in your SQL - but if when you do, learn to recognise the reported errors to help nut out the typo you've made - don't just ignore the error to sit & stare. While the error message doesn't always report the exact line/position of the actual problem, the hints it provides are typically consistent in some form - computers are dumb.
4. Upgrade your database - unrelated, just typically a good move ;-)

Scott

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!