| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: string functions returning garbage
Ed prochak wrote:
>
> You went thru a lot of details and never mentioned the data values
> that failed.
> What did they look like?
>
Most of them are entries like 040-00, 030+08 etc., with a lot of what I
thought were nulls. I had done a nvl substitution as Geoff suggested but
was only thinking about whether it fixed the problem, which it didn't.
Thinking about it from his message though I realized I didn't check to
see if the nulls were really null. Sure enough, it turned out they were
6 spaces.
I substituted NULL for the spaces in my test table and the query works fine. Suggested a "data cleansing" to the developers but that might not be viable since this is a legacy and investigators have all sorts of programs & queries running on it. Yes - it's all very strange and beyond my control.
I think my question now is whether this is a bug or a "feature." Shouldn't anything which can legitimately be inserted into a table, even a silly string of spaces, be able to be retrieved from the table using legitimate sql string functions?
Now that I've narrowed it down a bit I'll pass it on to tech support but am interested in opinions anyway.
> PS hopefully it's just a typo in your posting and not in the real
> query, but what kind of pattern is '%Moon%%' ? Why the double % at the
> end?
Good catch - I didn't notice that. Fixing it didn't change anything though.
Thanks for the enlightenment.
fdp
> Fred Pierce <fpierce_at_avialantic.com> wrote in message news:<3BFE9B72.E792C1D4_at_avialantic.com>...
> > 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
 |
 |