Re: fun debugging stuff on a friday

From: <TESTAJ3_at_nationwide.com>
Date: Fri, 10 Apr 2009 12:27:56 -0400
Message-ID: <OFA9C6AD72.86EBC39A-ON85257594.005A0784-85257594.005A72CC_at_lnotes-gw.ent.nwie.net>

To everyone who has replied,

1. not a cast issue
2. not a time trunc(date) issue
3. not what you think it is:

the answer lies in current_date as a variable,

select current_date from dual;

so even if you set a variable called current_date and assign a value to it, when sql executes its completely ignored.

took me a while to see it.

joe

---

Joe Testa, Oracle Certified Professional Senior Consultant
Data Engineering and Administration
Nationwide Investments

(Work) 614-677-1668
(Cell) 614-312-6715

Interested in helping out your marriage? Ask me about "Weekend to Remember"
Dec 11-13, 2009 here in Columbus.

From:
"Rich Jesse" <rjoralist_at_society.servebeer.com> To:
oracle-l_at_freelists.org
Date:
04/10/2009 11:45 AM
Subject:
Re: fun debugging stuff on a friday
Sent by:
oracle-l-bounce_at_freelists.org

Hey Joe,

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





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

Received on Fri Apr 10 2009 - 11:27:56 CDT