Re: getting ORA-01858 when using INSERT INTO SELECT ... (a non-numeric character was found where a numeric was expected)

From: Phil Singer <psinger1_at_chartermi.net>
Date: Sat, 01 Dec 2001 14:24:01 -0500
Message-ID: <3C092E51.34203B22@chartermi.net>

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.net

Oracle DBA Received on Sat Dec 01 2001 - 13:24:01 CST