Oracle Horror Morning

First, I need to say that the database I've primarily used for the last decade is MS SQL Server.

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

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.

No comments:

Post a Comment