Getting ORA-01858 when selecting from table 1

[slicendice]

Hi

I am trying to run a simple "SELECT * FROM..." query on a table (Oracle 8.1.7), and every time I run it, the first 11 rows get displayed OK and then the following error occurs:

Error: ORA-01858: a non-numeric character was found where a numeric was expected

I've identified the column in the 12th row that is causing the problem and it is a DATE column it is complaining about. I'm guessing it would be relatively easy to resolve this (just UPDATE the row/column with a valid new date) but I am intrigued to know what data it is that is in this column so I can try and work out how it got there - because if it was caused by the application that uses this database, it would indicate a possible bug.

Anyone got any ideas how I can display the erroneous data that is in this column? Or any ideas on how the data could have got into the column in the first place?

Thanks very much

 

[Turkbear]

Hi,
Please post your entire Sql statement..A simple Select * with no where clause should never cause your error..

 

[slicendice]

The statement is:

select * from assessments;

This gives the error:

ORA-01858: a non-numeric character was found where a numeric was expected

If I run:

select * from assessments
where rownum < 12;

then it works OK.

It is the data in the DATE column in the 12th row that is causing the problem. My question is how can I find out exactly what the data is, and how did it get there in the first place

Thanks

 

[Turkbear]

Hi,
Then I am truly stumped..A simple select statement should not give an error, since you are not trying to Input a Date and the error refers to inserting data or converting output:
From the docs:

ORA-01858 a non-numeric character was found where a numeric was expected

Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model.

Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation.



Also,do not rely on the < 12 to indicate that is is the 12th row since that it only indicates the 12th row returned by the query
Try adding an Order BY clause to the query and see when it fails..

Is assesments a simple Table? Please do a
desc assesments
and post the output..

 

[carp]

Is assessments a table or a view? If it is a view, then please post the underlying query. It's possible your view is trying to do something like a TO_NUM on a VARCHAR2 column and hitting a non-numeric character.

 

[carp]

Oops! I meant TO_NUMER, not TO_NUM!

 

[Turkbear]

Hi,
I bet you meant TO_NUMBER

Can also be from other functions that expect a number, like TO_CHAR.

 

[carp]

Yeah, TO_NUMBER - THAT'S the one!
Must be a virtual Monday!

 

[slicendice]

ASSESSMENT is a table not a view - it's details are:

SQL> DESC ASSESSMENT

Name Null? Type
------------------------------------- -------- ------------
ID NOT NULL NUMBER(8)
CLI_ID NOT NULL NUMBER(8)
DATE_OF_ASS DATE
TIME_OF_ASS NUMBER(4,2)
DATE_COMP DATE
TIME_COMP NUMBER(4,2)
RES_FOR_DELAY CHAR(10)
RES_NOT_COMP CHAR(10)
LOCATION CHAR(10)
GOAL CHAR(250)
CARRIED_OUT_BY CHAR(10)
PLANNED_COMP DATE
TYPE CHAR(10)
AUTH_MAN CHAR(10)
AUTH_ON DATE
AUTH_NOTES CHAR(255)
CARER_TYPE CHAR(1)
REASON CHAR(10)
READ_ONLY NOT NULL NUMBER(1)
REF_ID NUMBER(8)
CREATED_ON DATE
CREATED_AT NUMBER(4,2)
CREATED_BY NUMBER(8)
AMENDED_ON DATE
AMENDED_AT NUMBER(4,2)
AMENDED_BY NUMBER(8)
ACCESS_KEY CHAR(15)
UPDATE_KEY CHAR(15)
SHARED NUMBER(1)
PARENT_ID NUMBER(8)
OVERRIDE NUMBER(1)
SENSITIVE NUMBER(1)
SILENT NUMBER(1)
OWNER_TEM_ID CHAR(15)

It is the PLANNED_COMP field that has the dodgy data in it. I can do "SELECT <fieldname> FROM ASSESSMENT;" on every field in the table and it works OK...apart from PLANNED_COMP, when it falls over with ORA-08158. The ASS_ID of the row with the dodgy data is 392 - so I can run "SELET * FROM ASSESSMENT WHERE ASS_ID <> 392;" and it also works fine...

The thing that puzzles me is, how on earth did the bad data get in there in the first place? Surely Oracle would have thrown up an error when the INSERT that put the bad data in there was executed? Bizarre....!!

 

[hoinz]

Hi,
that assessments might be a view seems a good idea to me.
And the output given be the desc command does not prove it's a table.

Just to be sure, please run this query:

select owner, object_type from all_objects
where object_name = 'ASSESSMENT';

btw:
in your posts above you call your table first assessemnts (plural), and later on assessment (singular).
Is there a typo, or are there two tables/views?

regards

 

[slicendice]

SQL> select owner, object_type from all_objects where object_name = 'ASSESSMENT';

OWNER OBJECT_TYPE
------------------------------ ------------------
APPOWNER TABLE

And yes, the ASSESSMENT/ASSESSMENTS thing was indeed down to my incompetent typing...! ;-)

 

[hoinz]

Does this query work?

select dump(PLANNED_COMP,16) from ASSESSMENT
where ID = 392;

You wrote that the ASS_ID of the dodgy row is 392, but there is no ASS_ID in your DESC above.
So I guess this should be ID?

 

[slicendice]

Hi
Your query returns:

DUMP(PLANNED_COMP,16)
-------------------------------
Typ=12 Len=7: 34,e0,88,e,34,0,0

The "DUMP" function is a new one on me....does it show a hex representation of the data, or something like that?

(And sorry, yes - it should be ID!)

Thanks

 

[hoinz]

Hi,
yes, it's hex.
And typ gives the data type.
I am not quite sure about typ=12;
In Oracle 9i we have typ=13 for date format.

For comparing, could you please select the same from a few other rows as well?

regards

 

[slicendice]

ID DUMP(PLANNED_COMP,16)
------ -------------------------------
392 Typ=12 Len=7: 34,e0,88,e,34,0,0
467 Typ=12 Len=7: 78,68,7,1d,1,1,1
437 Typ=12 Len=7: 78,68,7,19,1,1,1
417 Typ=12 Len=7: 81,c2,9,f,1,1,1

ID's 467, 437 and 417 are all valid dates - looks like there's definately something fishy about 392 compared to the rest of them!

In Oracle 8i type 12 is DATE (I just looked it up in the manual!)

Thanks

 

[hoinz]

Hi,
a quick Google search gave me this:

http://www.ixora.com.au/notes/date_representation.htm

It seems that the your data are not valid date format.
Byte 6 and byte 7 should both be >0.

But I either don't understand, as you wrote above, how on earth did the bad data get in there in the first place? Surely Oracle would have thrown up an error when the INSERT that put the bad data in there was executed? Bizarre....!!

regards

 

[hoinz]

Hi,

me once again.
this somehow reminds me of a similar strange problem I encountered some years ago. The reason was hardware problems with a new installed disk.
In my case, a select from a column number(6,2) returned a value around a few billions. When I retried once again, I got the same nonsense. But when I waited an hour and retried then, I got the correct result. But soon there was some other similar nonsense.
Reason for this: The first reading from disk gave me some wrong bits; the next reading was done from cache, not from disk, and in cache there were the wrong bits. An hour later, the data where not in cache any more, and had to be read from disk again, and this time it worked.
I don't know whether this might help you. But can you (or perhaps did you) wait some time? Or can you reboot the machine in order to empty the cache?

regards

 

[slicendice]

Doing a dec dump plus a TO_CHAR gives:

ID DUMP(PLANNED_COMP) TO_CHAR(PLANNED..
--- ---------------------------------- -------------------
392 Typ=12 Len=7: 52,224,136,14,52,0,0 00/00/0000
467 Typ=12 Len=7: 120,104,7,29,1,1,1 29/07/2004
437 Typ=12 Len=7: 120,104,7,25,1,1,1 25/07/2004
417 Typ=12 Len=7: 129,194,9,15,1,1,1 15/09/2994

...which shows this value is totally screwed up for rec 392! Following the rules from that web link, the year is way back around 204BC and I'm still trying to work out what the 136th month of the year is!!

It's almost like the value stored in the table is an internal (memory) representation of a date - the above link mentions that dates stored in memory have no "+1" offset for the minutes and seconds, whereas minutes and seconds stored in a table should *always* be at least 1.

The database I'm using is on my local PC, so I've stopped and started it a few times and rebooted the machine, but doesn't make any difference.

I'm beginning to think this was down to a freak of nature - that somehow, and for some unbeknown reason, this one value got screwed up either after, or while, it was being written to the table.

 

[slicendice]

Well, I've spent a little while looking round for any further info on this but I think I'm going to have to leave it be and move on...!

Thanks for all your help and suggestions hoinz - much appreciated...

 

[techmon]

i was looking for how to convert a varchar to a number. I came to the right place. Thanks Caps.