Re: Oracle SQL bug in 9.2.0.8
Date: Thu, 10 Feb 2011 09:31:07 -0800 (PST)
Message-ID: <17afdca0-0e00-49cb-8816-177683ca2641_at_o21g2000prn.googlegroups.com>
On Feb 10, 12:25 am, Noons <wizofo..._at_yahoo.com.au> wrote:
> Geoff Muldoon wrote,on my timestamp of 10/02/2011 4:54 PM:
>
>
>
> > Someone else may be able to confirm that this is an acceptable
> > interpretation of the ANSI SQL99 standard for the comparison of CHAR
> > elements of unequal length where trailing whitespace only occurs.
>
> > Oracle seems to default strings in where clauses as CHAR.
>
> > The same behaviour does not happen for VARCHAR or VARCHAR2 elements, at
> > least in 11.2.
>
> Actually, if someone read the manual paragraph I pointed out, it'd be very clear
> what is going on. It's got nothing to do with SQL99.
Poking around a bit in the sqlplus manual, the only clue that things are defined default as char are the define and accept commands. Couldn't really find a clue that default strings are char, though in retrospect I guess the define definition should be one. A weak argument (very weak "we've always done it that way") against what asktom says about always using varchar2, for "historical reasons" I suppose. It explains some corrections I had to make to sqlplus scripts when I upgraded from char to varchar2, and didn't understand why at the time (or I probably did, but don't remember, fog of war in the coding deathmarch). Forehead slapping obvious, now. I'm still finding proprietary 4GL code that has strange extra code from the char days.
One would have to have access to the SQL99 specs to know if this default is actually considered there. My guess is it wouldn't be, it would be more likely the character types are defined, but defaults... vendor choice?
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2011/feb/08/if-co-worker-calls-you-old-man-can-you-sue-age-dis/Received on Thu Feb 10 2011 - 11:31:07 CST