Re: fun debugging stuff on a friday
- From: TESTAJ3@xxxxxxxxxxxxxx
- To: rjoralist@xxxxxxxxxxxxxxxxxxxxx
- Date: Fri, 10 Apr 2009 12:27:56 -0400
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@xxxxxxxxxxxxxxxxxxxxx>
To:
oracle-l@xxxxxxxxxxxxx
Date:
04/10/2009 11:45 AM
Subject:
Re: fun debugging stuff on a friday
Sent by:
oracle-l-bounce@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
