Re: fun debugging stuff on a friday

The things that jump out at me here are the huge assumptions that Oracle will always cast VARCHAR2 to or from DATE the same and that your locale date format will also never change.

Explicit casting and formatting is a much safer way to code, IMHO:

current_date := TO_DATE('30-OCT-07','DD-MON-RR'); ...
WHERE EFFECTIVE_DATE = TO_DATE('30-OCT-07','DD-MON-RR'); Be explicit! Implicit expectations/assumptions like this are showstoppers in database upgrades.

My Friday $.02,
Rich

> I get this code below, first glance says, this should work, Oracle is so
> much like C, gives you a gun, ammo, takes off the safety and has the
> trigger most of the way pulled while pointing the gun at your foot and
> assists you in pulling the trigger the rest of the way.
>
>
>
>
>
> declare
> current_date date;
> ctr number;
>
> begin
>
> current_date:='30-OCT-07';
>
>
> SELECT count(distinct SECURITY_ALIAS)
> into ctr
> FROM DATAMARTDBO.SECURITY_DETAILS
> WHERE EFFECTIVE_DATE = current_date;
>
> dbms_output.put_line('CTR1:'||ctr);
>
>
>
> SELECT count(distinct SECURITY_ALIAS)
> into ctr
> FROM DATAMARTDBO.SECURITY_DETAILS
> WHERE EFFECTIVE_DATE = '30-OCT-07';
>
> dbms_output.put_line('CTR2:'||ctr);
>
> end;
> /
>
> CTR1: 0
> CTR2: 35178

--
http://www.freelists.org/webpage/oracle-l