CODEWITHSUNDEEP
sqloracle-database
user2488578
user2488578

Reputation: 916

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

I have the below two tables created.

CREATE TABLE DELETE_CUSTOM
(
 column1 varchar2(30),
 column2 varchar2(30)
)

CREATE TABLE DELETE_CUSTOM_HIST
(
 column1 varchar2(30),
 column2 varchar2(30),
 ARCHIVAL_DATE DATE
)

I used to insert data into DELETE_CUSTOM_HIST like below.

INSERT INTO DELETE_CUSTOM_HIST
      (SELECT DE.*, to_date('12/31/2017','mm/dd/yyyy')
         FROM DELETE_CUSTOM DE);

Now, I had to add a new column to both the tables.

ALTER TABLE DELETE_CUSTOM ADD column3 VARCHAR2(30);
ALTER TABLE DELETE_CUSTOM_HIST ADD column3 VARCHAR2(30);

If I try to insert data to DELETE_CUSTOM_HIST table with the same below INSERT statementm, I am getting ORA-01858 error.

INSERT INTO DELETE_CUSTOM_HIST
      (SELECT DE.*, to_date('12/31/2017','mm/dd/yyyy')
         FROM DELETE_CUSTOM DE);
--ORA-01858: a non-numeric character was found when a numeric was expected.

What change I need to do to my INSERT statement to overcome this issue.

Note : The above insert statement is in PLSQL package. The above two tables are actually having many columns. I have just mentioned few columns to explain my problem in a simple way.

Upvotes: 1

Views: 11087

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Two points. The first is to always list the columns explicitly for such statements. Don't use * in production code -- unless you really know what you are doing.

INSERT INTO DELETE_CUSTOM_HIST (column1, column2, column3, archival_date)
     SELECT DE.column1, DE.column2, DE.column3, DATE '2017-12-31'
     FROM DELETE_CUSTOM DE;

Notes (in addition to listing the columns):

  • Parentheses are not appropriate around the SELECT.
  • Use date literals. Oracle supports the ANSI standard here, use it.
  • Are you sure you just want the date portion for the archival date?

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65228

you have :

CREATE TABLE DELETE_CUSTOM_HIST
(
 column1 varchar2(30),
 column2 varchar2(30),
 ARCHIVAL_DATE DATE,
 column3 varchar2(30)
);

trying to INSERT

a varchar2 type column ( COLUMN3 column of DELETE_CUSTOM table ) column to

a date type column ( ARCHIVAL_DATE column of DELETE_CUSTOM_HIST table ).

The raised error is due to this mismatch with varchar2 and date.

Upvotes: 1

SevincQurbanova
SevincQurbanova

Reputation: 366

INSERT INTO DELETE_CUSTOM_HIST(column1,column2,column3)
      (SELECT de.column1,de.column2,de.column3, 
to_date('12/31/2017','mm/dd/yyyy')
         FROM DELETE_CUSTOM DE);

explicitly write column list and try again.

Upvotes: 2

Related Questions