Breaking the Rules – why sometimes it’s OK to have a standalone PL/SQL Function

It was late. We were snuggled up on the sofa, watching a Romcom and debating whether to go to bed or see it through to the bitter( well, sickly sweet) end.

Wearily, I made the point that in the end the film would follow Heigl’s Iron Law of Romcom which can be summarised as “Katherine always gets her man”.

Deb begged to differ. Her argument was that, for every Colin Firth, riding into the sunset with his Bridget Jones, there’s a poor( largely blameless) Patrick Dempsey whose immediate future includes long-evenings alone in front of the telly and shopping for microwave meals for one.
The point is that even the most rigid rules tend to have their exceptions.

The star of this post is the oft-quoted rule that PL/SQL program units should always be incorporated into a Package.
There are special cameo appearances by “Never use Public Synonyms” and the ever popular “Never grant privileges to Public”.

Why Grouping Functions and Procedures in Packages is a Good Idea

“Always use a package. Never use a standalone procedure.”
This is a quote from Tom Kyte.
More precisely, it’s a partial quote. We’ll come back to that in a moment.

Mr Kyte goes on to expound the virtues of packages because they ( quoting once again)…

“- break the dependency chain (no cascading invalidations when you install a new package body — if you have procedures that call procedures — compiling one will invalidate your database)

– support encapsulation — I will be allowed to write MODULAR, easy to understand code — rather then MONOLITHIC, non-understandable procedures

– increase my namespace measurably. package names have to be unique in a schema, but I can have many procedures across packages with the same name without colliding

– support overloading

– support session variables when you need them

– promote overall good coding techniques, stuff that lets you write code that is modular, understandable, logically grouped together….

Well that all seems fairly comprehensive. So why are we even having the discussion ? Well, it comes back to the rest of the above quote, which tends to get missed when rules like this are invoked.

The full quote is actually :

“Always use a package. Never use a standalone procedure except for demos, tests and standalone utilities (that call nothing and are called by nothing).”

Having recently covered the fact that “unless your writing tests” should be appended to any rule relating to Oracle code, I’m going to focus on…

An Application-Independent Standalone function

It just so happens that I have one of these lying around
The radix_to_decimal function takes a string representation of a number in a base between 2 and 36 and returns it’s decimal equivalent.
The function does not read from or write to any application tables :

create or replace function radix_to_decimal( i_number in varchar2, i_radix in pls_integer)
    return pls_integer
-- Function to return the decimal representation of i_number in i_radix.
-- This handles bases between 2 and 36 (i.e. any base where numeric values are represented by alphanumeric characters)
    ASCII_0 constant pls_integer := 48; -- the ascii value for '0'
    ASCII_9 constant pls_integer :=  57; -- the ascii value for '9'

    revnum varchar2(38);
    rtnval pls_integer := 0;
    digit varchar2(1);

    e_missing_param_value exception;
    e_invalid_radix exception;
    e_invalid_digit_for_base exception;
    -- Parameter sanity checks
    if i_number is null or i_radix is null then
        raise e_missing_param_value;

    elsif i_radix not between 2 and 36 then
        raise e_invalid_radix;

    elsif i_radix = 10 then
        return i_number;

    -- Validate that i_number is actually a valid i_radix value.
    elsif (i_radix > 10 and instr( i_number, chr(55 + i_radix),1,1) > 0)
        or ( i_radix < 10 and instr( i_number, i_radix, 1, 1) > 0) 
            raise e_invalid_digit_for_base;
    end if;

    -- Reverse the i_number string so we can loop through and sum the decimal numbers represented by each character
    -- without having to check the length of i_number.
    -- The REVERSE function is a SQL, rather than PL/SQL built-in, hence...

    select reverse(i_number) into revnum from sys.dual;

    for i in 1..length(revnum) loop
        digit := substr(revnum, i, 1);
        if ascii(digit) between ASCII_0 and ASCII_9 then
            rtnval := rtnval + ( digit * power(i_radix, i - 1));
            -- letters in bases above 10 are always offset from 10 - e.g. A = 10, B = 11 etc.
            -- so, subtracting 55 from the ascii code of the upper case letter will give us the decimal value
            rtnval := rtnval + ( ( ascii( upper( digit)) - 55) * power( i_radix, i - 1) );
        end if;
    end loop;
    return rtnval;


    when e_missing_param_value then
        raise_application_error( -20000, 'Both a number and a base must be specified');

    when e_invalid_radix then
        raise_application_error( -20001, 'This function only converts bases 2 - 36');

    when e_invalid_digit_for_base then
        raise_application_error( -20002, 'Number '||i_number||' is not a valid '||i_radix||' number.');

end radix_to_decimal;

Here’s a quick demo of the function in action….

select radix_to_decimal('101', 2) from dual;


select radix_to_decimal('401', 8) from dual;


select radix_to_decimal('7E0', 16) from dual;


I’ve also uploaded the function to LiveSQL so feel free to have a play around with it.

Meanwhile, back in the database, to make this function generally available, grant execute to everyone…

grant execute on radix_to_decimal to public

What’s that ? I’ve violated the principle of least privilege ? Well, you may have a point. However, that principle has been weighed against the practicality of being able to re-use this code ( the principle of Don’t Repeat Yourself).
Whilst, under most circumstances, security wins out, there are (in Oracle at least) one or two exceptions as you can see by running…

select count(*)
from all_tab_privs
where privilege = 'EXECUTE'
and grantee = 'PUBLIC'

In order to make it easy to call this function, we don’t want to have to remember which schema we happened to put this in, so we’re going to create a public synonym…

create or replace public synonym radix_to_decimal for mike.radix_to_decimal

Once again, you may raise the very valid issue of namespace pollution caused by the use of Public Synonyms.
Once again, I’ve chosen pragmatism over principle in this specific instance.
Of course, if the next version of oracle contains a function called radix_to_decimal you can come back and say “I told you so !”


2 thoughts on “Breaking the Rules – why sometimes it’s OK to have a standalone PL/SQL Function

  1. Thanks, Mike. Interesting post. Another cause for “Told you so” might be when you realize, “Oh, it’d be nice to have a decimal_to_radix function, ah, and wow also a few more conversion functions” and suddenly you realize you should have started with a conversion_utils package. 🙂

    • Steven,

      That is a fair point. I think that the correct course of action here would depend on the context in which you find yourself.
      It may be that there are multiple applications on a single instance, each with their own codebase, each with their own utils package(s).

      It may also be that each of these util packages contains exactly one member because the application developers never got around to building it out.

      If having a standalone function means that this functionality doesn’t get replicated across n applications because devs don’t forget which utils package it’s in and write it all over again, then I’d suggest that it is worth considering at the very least.


Leave a Reply

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

You are commenting using your 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