Re: string functions returning garbage

I'd think about adding 'NVL' to each clause in the 'where' $ eg
(to_number(NVL(ltrim(substr(upleft,4,3)) ,0)) >= -45 Just to make absolutly sure there is a numeric value

Geoff Reader

Analyst Programmer
Admin Computing
University of Brighton

Fred Pierce wrote:
>
> Oracle 8.1.6.3.0 on unix. Problem duplicated on 8.1.7/Windows 2k
> (exp/imp
> tables)
>
> One of our developers sent me the following query, saying that it
> resulted
> in an "invalid number" error. To summarize, the string functions are
> sometimes returning garbage (pointer problem?). Before I hand it to tech
> support I thought it was interesting enough to share with the group.
> Also
> thought someone might know of a bug or see something obviously wrong in
> the
> query - though I would assume if the latter Oracle would have a more
> polite
> way of expressing it.
>
> select planet,id1,
> substr(upleft,4,3),
> substr(lwrrit,4,3),
> substr(upleft,1,3),
> substr(lwrrit,1,3)
> from map where planet like '%Moon%%' and
> (to_number(ltrim(substr(upleft,4,3))) >= -45 and
> to_number(ltrim(substr(lwrrit,4,3))) <= -45) and
> (to_number(ltrim(substr(upleft,1,3))) <= 180 and
> to_number(ltrim(substr(lwrrit,1,3))) >= 180) and
> id1map in (select id1map from map_inv)
>
> I verified the invalid number result, and using Embarcadero's DBArtisan
> was able to get a
> little more info (when the result didn't cause it to crash)
>
> Garbage result (varies but this is typical):
>
> Planet id1
> Oracle8i Enterprise Edition -455552554655535500 ...zillion zeros
>
> upleft 4 3 lwrrit 4 3 upleft 13 lwrrit 1 3
> Production P
x
P
P
x
P
P
x
P

>
> After commenting out clauses from subquery to second (to_number)
> condition
> the query gets sensible results. Once that has happened I uncomment
> everything and the query works:
>
> Moon 868 -42 -45 016 007
>
> Although I didn't see anything strange in the tables, I ran dbverify
> which
> came up clean.
>
> Here are the table structures:
>
> MAP -
>
> CENTR Y VARCHAR2 6
> DATENT Y VARCHAR2 10
> FRC Y VARCHAR2 2
> ID1 Y NUMBER 7 7 0
> ID1MAP Y VARCHAR2 12
> LASTUP Y VARCHAR2 10
> LWRLFT Y VARCHAR2 6
> LWRRIT Y VARCHAR2 6
> MAPTYP Y VARCHAR2 4
> PLANET Y VARCHAR2 10
> PUBYR Y VARCHAR2 2
> QUANTY Y NUMBER 6 6 0
> SCALE Y VARCHAR2 4
> SOURCE Y VARCHAR2 4
> TITLE Y VARCHAR2 30
> UPLEFT Y VARCHAR2 6
> UPRITE Y VARCHAR2 6
>
> AREA N VARCHAR2 13
> ID1 N NUMBER 7 7 0
> ID1MAP N VARCHAR2 12
> QUANTY Y NUMBER 4 4 0
> SITE N VARCHAR2 4
>
> I think the Planet Oracle and its connection with the Enterprise is a
> clue also, but just can't decide what it means...
>
> fdp
>
> "Toulouse was too loose; he had to lose"
>
> --------------------------------------------------------------------
> Fred Pierce (DNRC)- fpierce_at_avialantic.com
> Mid-Atlantic Aviation on the Web - http://www.avialantic.com
> ** Mid Atlantic Air Museum WWII Weekend Airshow June 7-9 2002 and
> Planes, Trains, & Automobiles Transporation Show Sept. 21-22 2002
> - www.maam.org **
> *** World Airshow News - www.worldairshownews.com **
> ---------------------------------------------------------------------
Received on Mon Nov 26 2001 - 08:11:03 CST