Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: getting ORA-01858 when using INSERT INTO SELECT ... (a non-numeric character was found where a numeric was expected)
Jens Kisters wrote:
>
> Hey there,
>
> I've encountered a problem i couldnt find an answer to in the newsgroup
> archives.
>
> i have two identical tables.
>
> SQL> desc backup_scores;
> Name Null? Typ
> ----------------------------------------------------- -------- ------------
> -
> WEEK NOT NULL DATE
> NICK NOT NULL VARCHAR2(30)
> GAMEID NOT NULL NUMBER(9)
> GAMEDATE NOT NULL DATE
> SSO_USER_ID NOT NULL NUMBER(12)
> SCORE NOT NULL NUMBER(14,4)
>
[snip]
>
> INSERT INTO TF1b.backup_scores
> SELECT * FROM TF1b.scores
> WHERE week
> BETWEEN TO_DATE('20000101000000','YYYYMMDDHH24MISS')
> AND TO_DATE('20011129235959','YYYYMMDDHH24MISS')
> AND TO_DATE('20000101000000','YYYYMMDDHH24MISS')
> < TO_DATE('20011129235959','YYYYMMDDHH24MISS')
>
> it fails giving me an error
>
> ORA-01858: a non-numeric character was found where a numeric was expected
>
Your NICK and WEEK columns are swapped in your table definitions. Thus, when you so a SELECT * INTO... you are trying to put whatever is in NICK into WEEK. Oracle assumes you want to do this, therefore it is trying to do a conversion of NICK into a date type. When it can't, you get the ORA-01858.
The solution is to explicitly select the columns you want, in the order you need.
Phil Singer | psinger1_at_chartermi.netOracle DBA Received on Sat Dec 01 2001 - 13:24:01 CST