The Movember Ashes Curse and PL/SQL Parameters

A wise man once said that those who fail to learn the lessons of history are doomed to repeat them.
Time for a quick history lesson…
England’s recent supremacy in Ashes contests have a commmon theme running through them. In none of the last three series has any Australian fast bowler sport any facial hair.
The bristling tache has been a feature of potent Australian attacks since time immemorial.
From Frederick “The Demon” Spofforth sporting the face furniture that inspired an England batting collapse which gave birth to the Ashes legend, through Dennis Lillee and (occasionally) Jeff Thompson, the tache reigned supreme.
Having reached an exuberant zenith whilst working with Merv Hughes, the tache took a lower profile for a time as the top-half of Jason Gillespie’s beard.
There was a time during the 80′s when England seemed to have cottoned on to the power of the tache and managed to fight fire with fire with notable contributions evident on the upper lips of Botham, Gooch and Lamb.
More recently however, the moustache’s mystic ability to make an English batting line-up do a passable impression of a deck-chair seemed to have been lost in the mists of time.
Then along came Mitchell Johnson’s Movember Mush Mantle.
Johnson himself has quite a respectable test record, but it seems that he may have re-discovered the secret power of the mighty moustache.
As we’ve got a couple of weeks before the second test begins, the moustache has a bit of time on it’s handlebars so has kindly agreed to illustrate the fun and games to be had with assigning default values to PL/SQL parameters.

Let’s begin with the following function…

CREATE OR REPLACE FUNCTION bowler_effectiveness_fn
( 
    i_has_tache VARCHAR2
)
    RETURN VARCHAR2
AS
BEGIN
    IF i_has_tache = 'Y' THEN 
        RETURN 'On for a five-for';
    ELSE
        RETURN 'None for plenty';
    END IF;
END;
/

If we now invoke this function without specifying a parameter value…

SELECT bowler_effectiveness_fn
FROM dual;

…we get…

ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to
'BOWLER_EFFECTIVENESS_FN'

So, it looks like the i_has_tache parameter is mandatory.
However, we can make it optional by specifying a default value to assign it if none is specified in the call…

CREATE OR REPLACE FUNCTION bowler_effectiveness_fn
( 
    i_has_tache VARCHAR2 DEFAULT 'N'
)
    RETURN VARCHAR2
AS
BEGIN
    IF i_has_tache = 'Y' THEN 
        RETURN 'On for a five-for';
    ELSE
        RETURN 'None for plenty';
    END IF;
END;
/

If we run the same query as before…

SQL> SELECT bowler_effectiveness_fn FROM dual;

BOWLER_EFFECTIVENESS_FN
--------------------------------------------------------------------------------
None for plenty

SQL> 

Of course, if we do specify a value in the call then this will be used in place of the default…

SQL> SELECT bowler_effectiveness_fn('Y') from dual;

BOWLER_EFFECTIVENESS_FN('Y')
--------------------------------------------------------------------------------
On for a five-for

SQL> 

So, the default value acts a bit like an NVL right ? I mean…

SQL> SELECT bowler_effectiveness_fn(i_has_tache => NULL) FROM dual;

BOWLER_EFFECTIVENESS_FN(I_HAS_TACHE=>NULL)
--------------------------------------------------------------------------------
None for plenty

SQL> 

OK, let’s make a change to the default value from N to Y :

CREATE OR REPLACE FUNCTION bowler_effectiveness_fn
( 
    i_has_tache VARCHAR2 DEFAULT 'Y'
)
    RETURN VARCHAR2
AS
BEGIN
    IF i_has_tache = 'Y' THEN 
        RETURN 'On for a five-for';
    ELSE
        RETURN 'None for plenty';
    END IF;
END;
/

Passing in NULL should now result in a confirmation of our macho bowling prowess…

SQL> SELECT bowler_effectiveness_fn(i_has_tache => NULL) FROM dual;

BOWLER_EFFECTIVENESS_FN(I_HAS_TACHE=>NULL)
--------------------------------------------------------------------------------
None for plenty

SQL> 

Hmmm…maybe Mitchell has reached for the razor ? Let’s take a closer look…

CREATE OR REPLACE FUNCTION bowler_effectiveness_fn
( 
    i_has_tache VARCHAR2 DEFAULT 'Y'
)
    RETURN VARCHAR2
AS
BEGIN
    IF i_has_tache IS NULL THEN
        RETURN 'Face fuzz not specified.';
    ELSIF i_has_tache = 'Y' THEN 
        RETURN 'On for a five-for';
    ELSE
        RETURN 'None for plenty';
    END IF;
END;
/

Run the query again and we get…

SQL> SELECT bowler_effectiveness_fn(i_has_tache => NULL) FROM dual;

BOWLER_EFFECTIVENESS_FN(I_HAS_TACHE=>NULL)
--------------------------------------------------------------------------------
Face fuzz not specified.

SQL> 

So, a stored program unit parameter can be specified as NULL, even if it has a default value defined.
If this is not what you want to happen, you need to check the parameter values in your code…

CREATE OR REPLACE FUNCTION bowler_effectiveness_fn
( 
    i_has_tache VARCHAR2 DEFAULT 'Y'
)
    RETURN VARCHAR2
AS
BEGIN
    --
    -- Make sure that the mandatory i_has_tache parameter
    -- is set.
    --
    IF i_has_tache IS NULL THEN
        RAISE_APPLICATION_ERROR( -20000, 'Face fuzz not specified.');
    END IF; 
    -- end of parameter validation
    IF i_has_tache = 'Y' THEN 
        RETURN 'On for a five-for';
    ELSE
        RETURN 'None for plenty';
    END IF;
END;
/

When we run our query now, we should be told explicitly that a parameter value is required :

SQL> SELECT bowler_effectiveness_fn(i_has_tache => NULL) FROM dual;
SELECT bowler_effectiveness_fn(i_has_tache => NULL) FROM dual
       *
ERROR at line 1:
ORA-20000: Face fuzz not specified.
ORA-06512: at "MIKE.BOWLER_EFFECTIVENESS_FN", line 13
ORA-06512: at line 1


SQL> 

The other thing to bear in mind is that, even if you define your parameters with an anchored declaration, the user can pass in any value up to the maximum length of the datatype of the parameter. So, we may well want to make sure that the function is being called only with those values that we expect to handle :

CREATE OR REPLACE FUNCTION bowler_effectiveness_fn
( 
    i_has_tache VARCHAR2 DEFAULT 'Y'
)
    RETURN VARCHAR2
AS
BEGIN
    --
    -- Make sure that the mandatory i_has_tache parameter
    -- is set to a valid value
    --
    IF NVL(i_has_tache, 'X') NOT IN ('Y', 'N') THEN
        RAISE_APPLICATION_ERROR(-20000, 'Parameter i_has_tache must be set to Y or N'); 
    END IF;
    -- end of parameter validation
    IF i_has_tache = 'Y' THEN 
        RETURN 'On for a five-for';
    ELSE
        RETURN 'None for plenty';
    END IF;
END;
/

NOTE – Thanks to Ronald for pointing out my erroneous assertion that an anchored declaration will limit the length of the allowable input parameter to the maximum length of the column. See comments below.
As we reach the end of November, the straw at which England Cricket Fans are clutching is that maybe Mitchell is not, after all, a keen student of the game’s history and that maybe he will indeed emerge clean-shaven at Adelaide.
If not, England may well be in for a series of torrid tache tyrrany.

About these ads

2 thoughts on “The Movember Ashes Curse and PL/SQL Parameters

  1. Hello Mike,
    You wrote: “The other thing to bear in mind is that, unless you define your parameters with an anchored declaration, the user can pass in any value up to the maximum length of the datatype of the parameter.”
    Unless I’m mistaken, parameters are never restricted in length other than by their datatype, even when defined with anchored declaration; only the type is validated, not the length restriction.
    Keep up the good work of writing such nice-to-read entries!
    Kind regards,
    Ronald
    (http://ronaldsoracle.wordpress.com)

    • Ronald,

      let’s have a look at a test case, based on one of my favourite films.
      The reason for this theme will shortly become apparent…

      create table characters
      (
          character_name VARCHAR2(10)
      )
      /
      
      insert into characters( character_name) values('Megamind')
      /
      insert into characters( character_name) values( 'Metro Man')
      /
      commit;
      

      Now for a simple function…

      create or replace function is_character
      ( 
          i_character in characters.character_name%TYPE
      )
          return varchar2
      is
          l_dummy pls_integer;
      begin
          select 1
          into l_dummy
          from characters
          where upper(character_name) = upper(i_character);
          return 'Y';
      exception when no_data_found then
          return 'N';
      end;
      /
      

      …and now to test it…

      
      SQL> select is_character('Megamind') from dual;
      
      IS_CHARACTER('MEGAMIND')
      --------------------------------------------------------------------------------
      Y
      
      SQL> select is_character('Yoda') from dual;
      
      IS_CHARACTER('YODA')
      --------------------------------------------------------------------------------
      N
      

      Now let’s pass in a string longer than 10 characters…

      SQL> select is_character('The Anti-Kyte') from dual;
      
      IS_CHARACTER('THEANTI-KYTE')
      --------------------------------------------------------------------------------
      N
      

      In the words of Megamind himself, “You were right. I was…less right”.

      I’ve now corrected the post.
      Thanks for pointing this out.

      Mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s