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) -- is 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; begin -- 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) then 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)); else -- 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; exception 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; RADIX_TO_DECIMAL('101',2) ------------------------- 5 select radix_to_decimal('401', 8) from dual; RADIX_TO_DECIMAL('401',8) ------------------------- 257 select radix_to_decimal('7E0', 16) from dual; RADIX_TO_DECIMAL('7E0',16) -------------------------- 2016
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 !”