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

Finding stuff in ALL_VIEWS using XML

Quite some time ago when I first started blogging I wrote a post regarding limitations on searching the ALL_VIEWS dictionary view.

Simply put, if you would like to run a query such as this to find those views that contain the text "booking_no", you're not going to get very far.

SQL> select count(*)
2  from user_views
3  where text like '%booking_no%';
where text like '%booking_no%'
*
ERROR at line 3:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

I can't remember where I found this, but there is a way to do perform this search without pumping your data into a temporary table.

select count(*)
from user_views 
where upper(dbms_xmlgen.getxml('select text from user_views where view_name = '''||view_name||'''')) 
  like upper('%booking_no%')

It's essentially looking for "booking_no" within the result of a query that has been converted into canonical XML format.

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <TEXT>SELECT r.code,b.booking_no,e.start_date,e.end_date,o.org_id,o.name
FROM   resources r,bookings b,events e,organisations o
WHERE  r.code = b.resource_code
AND    b.event_no = e.event_no
AND    o.org_id = e.org_id</TEXT>
 </ROW>
</ROWSET>

A word of warning, it is CPU (and probably memory) intensive, so you might like to limit to a particular set of schemas instead of just searching ALL_VIEWS.

Turns out XML has some nifty uses after all ;-)

Beyond NVL

If there's one thing I've ever learned while developing in Oracle, there is always more than one way to solve a problem.

The question is - have you done it in the simplest, most intuitive manner?

That's the bar to set, because it may be you the revisits some code 2, 6, 12 months later...

Recently, I came across some code like this:

CASE
    WHEN NVL(custom_value, normal_value) = normal_value THEN
      NULL
    ELSE
      normal_value
    END AS alt_value

I had a fair idea what it was trying to resolve, but I wasn't sure and I still had to run some scenarios to be sure.
Basically, this was the 2nd of two fields that showed a normal value and a custom value. The first field was simply
NVL(custom_value, normal_value)

The second field should be:
If there is a custom value, show the normal value, otherwise show nothing.

This case statement does this, but more like:
If there isn't a custom value, compare the normal value with itself - if it is the same, show nothing, otherwise show the normal value.

Que?

I don't know if it's lack of familiarity with the NULL-related functions, or some people don't immediately consider them.
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions002.htm#CJAFHIFF

While mentally trying to convert it to something simpler, I simplified to
NULLIF(normal_value, NVL(custom_value, normal_value))

Which translates to:
If normal value is the same as itself if the custom value is not present, then use the normal value, otherwise nothing.

Then I recognised it could be simplified further, just like resolving a fraction ;-)
NVL2(custom_value, normal_value, NULL)

If custom value is not null, use normal value, otherwise show nothing.

Which reads just like my requirement - using one function, referencing each field once only.

Super.

Scott.

Added 11 April 2012 - in response to comments

with data as 
 (select 'Mouse' widget, 10 normal_value, null custom_value from dual union all
  select 'Laptop', 1000, 800 from dual union all
  select 'Mixer', 30, 30 from dual union all
  select 'Lost sock', null, 1 from dual)
select 
   widget
  ,normal_value
  ,custom_value
  ,nvl(custom_value, normal_value) first_field
  ,case when nvl(custom_value, normal_value) = normal_value then
    null
   else
    normal_value
  end as alt_value_orig
 ,nvl2(custom_value, normal_value, null) alt_value_scott
 ,case when custom_value <> normal_value then normal_value end alt_value_anon
 ,coalesce(custom_value, normal_value) alt_value_hayland
from data; 

Cheers

Updating key-preserved inline views

I learnt something today - there is a scenario where you need update privileges on a table you aren't updating.

Here is my example - I have a table TRAIN_APEX.RESOURCES that I would like to update based on TRAIN.RESOURCE_RATES. So the key factor here is my source table is in a different schema to my destination table - and I only have select privileges on resource_rates.

TRAIN_APEX> SELECT privilege, grantee, table_name FROM all_tab_privs where table_name = 'RESOURCE_RATES';

PRIVILEGE            GRANTEE              TABLE_NAME
-------------------- -------------------- --------------------------------------------------------
SELECT               TRAIN_APEX           RESOURCE_RATES

1 row selected.

This means when I attempt to run an update that uses an elegant key-preserved in-line view, I can't do it!

TRAIN_APEX> UPDATE
  2    (SELECT r.type_code
  3           ,a.standard_rate
  4           ,r.code
  5           ,r.daily_rate
  6     FROM train_apex.resources r
  7         ,train.resource_rates a
  8     WHERE a.type_code = r.type_code)
  9  SET daily_rate = standard_rate;
       ,train.resource_rates a
              *
ERROR at line 7:
ORA-01031: insufficient privileges

I've had a look through the documentation for views and update statements, and I can't see it as a pre-requisite.

Out of curiosity I defined the statement as an actual view to confirm the base table is key-preserved. I knew this already because I've successfully run this statement when the tables are in the same schema, or in scenarios where my account has UPDATE ANY TABLE.

TRAIN_APEX> CREATE VIEW resources_vw AS
  2  SELECT r.type_code
  3        ,a.standard_rate
  4        ,r.code
  5        ,r.daily_rate
  6  FROM train_apex.resources r
  7      ,train.resource_rates a
  8  WHERE a.type_code = r.type_code;

View created.

Elapsed: 00:00:00.05
SQL>
SQL> @view_dml resources_vw

COLUMN_NAME          INSERTABL UPDATABLE DELETABLE
-------------------- --------- --------- ---------
TYPE_CODE            YES       YES       YES
STANDARD_RATE        NO        NO        NO
CODE                 YES       YES       YES
DAILY_RATE           YES       YES       YES

4 rows selected.

So instead, I have to run a "normal" update with a condition to check for presence of rows in my source table, which also means my update isn't as efficient.

UPDATE train_apex.resources r
SET daily_rate =
  (SELECT standard_rate
   FROM train.resource_rates a
   WHERE a.type_code = r.type_code)
WHERE EXISTS
  (SELECT NULL
   FROM train.resource_rates a
   WHERE a.type_code = r.type_code)

Do you think this is a bug or expected behaviour?

Scott