I needed to write a simple function like so:
is_holiday(in_code in varchar2, in_year in integer, in_month in integer, in_day in integer)
that returns a boolean.
First, I wrote a few tests like these:
select is_holiday('xyz', 2006, 8, 1) from dual; -- false
select is_holiday('xyz', 2009, 11, 1) from dual; -- true
When I ran the tests after implementing the function I got the following messages:
SQL Error: ORA-06552: PL/SQL: Statement ignored ORA-06553: PLS-382: expression is of wrong type
I later learned that this is because booleans aren't allowed inside SQL like that. A sort of misguiding and not very helpful error message IMHO.
On my quest of finding a solution, I also tried named params like so:
select is_holiday(in_sm_center_code => 'xyz', in_year => 2006, in_month => 8, in_day => 1) from dual;
That gave me the horror:
ORA-00907: missing right parenthesis
What?!?
Once again, PL/SQL programs differ from the SQL statements like that, where the params can't be named, but must be in positional form.
Why not simply state that in the error dialog box?
Thanks to this blog: oraclequirks.blogspot.com, I got my head around these issues and solved the task at hand.
