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