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

Question

There is a row that I can not enter the table

CREATE TABLE CLIENTE(
RUT VARCHAR2(10) CONSTRAINT CLIENTE_PK PRIMARY KEY,
NOMBRE VARCHAR2(20) CONSTRAINT NOTNULL_NOM_CLIENTE NOT NULL,
APELLIDOP VARCHAR2(30) CONSTRAINT NOTNULL_APPEP_CLIENTE NOT NULL,
APELLIDOM VARCHAR2(30) CONSTRAINT NOTNULL_APPEM_CLIENTE NOT NULL,
DIRECCION VARCHAR2(100) CONSTRAINT NOTNULL_DIR_CLIENTE NOT NULL,
TELEFONO NUMBER(8) CONSTRAINT NOTNULL_FONO_CLIENTE NOT NULL,
EMAIL VARCHAR2(255) CONSTRAINT NOTNULL_MAIL_CLIENTE NOT NULL,
FECHA_NACIMIENTO DATE CONSTRAINT NOTNULL_NACIMIENTO_CLIENTE NOT NULL,
SEXO CHAR(1) CONSTRAINT NOTNULL_SEXO_CLIENTE NOT NULL,
COD_CIUDAD NUMBER(3) CONSTRAINT NOTNULL_COD_CIUDAD NOT NULL,
CONSTRAINT CLIENTE_CIUDAD_FK FOREIGN KEY (COD_CIUDAD) REFERENCES CIUDAD (COD_CIUDAD)
);

It is the only row that I can not enter

INSERT INTO CLIENTE VALUES('08798234-9','Luis','Garcia','Ramirez','Málaga 753',78452378,'lgarcia@gmail.com','12-APR-1977','M',1);

The Error:

INSERT INTO CLIENTE VALUES
('08798234-9','Luis','Garcia','Ramirez','Málaga 753',78452378,'lgarcia@gmail.com','12-APR-1977','M',1)
Informe de error -
ORA-01858: a non-numeric character was found where a numeric was expected

Did you just publish someone's actual phone number and email address? That aside, check you default date format is correct. if in doubt use to_date() instead of a string to test. — Gary_W, Dec 10 '17 at 02:40
@Gary_W if I put this data it is saved, but I do not know why the other one does not. `INSERT INTO CLIENTE VALUES('12378095-8','Antonio','Lopez','Tapia','Bravante 1345',97642378,'alt@gmail.com','18-JUL-1985','M',4);` — Derezed, Dec 10 '17 at 02:51
It has to be the special character in the address. is your data unicode? If so the database's characterset must support it. Replace the accented a and see if the insert works. If so, there's your culprit. — Gary_W, Dec 10 '17 at 04:11
Your data appears to be Spanish, suggesting your database session mght - at least partly - be set up for that locale; but you’ve used English month abbreviations. I believe JUL is still valid, and your original would work with ABR instead of APR? Which is all covered in the duplicate, but that’s what is wrong here. Anyway, dont’t rely on NLS settings (your date format seems to expect MM not MON) or implicit conversion, and don’t use month names if you can avoid them. — Alex Poole, Dec 10 '17 at 09:17

score 0 · Answer 1 · answered Dec 10 '17 at 02:12

0

Unable to replicate:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE CLIENTE(
RUT VARCHAR2(10) CONSTRAINT CLIENTE_PK PRIMARY KEY,
NOMBRE VARCHAR2(20) CONSTRAINT NOTNULL_NOM_CLIENTE NOT NULL,
APELLIDOP VARCHAR2(30) CONSTRAINT NOTNULL_APPEP_CLIENTE NOT NULL,
APELLIDOM VARCHAR2(30) CONSTRAINT NOTNULL_APPEM_CLIENTE NOT NULL,
DIRECCION VARCHAR2(100) CONSTRAINT NOTNULL_DIR_CLIENTE NOT NULL,
TELEFONO NUMBER(8) CONSTRAINT NOTNULL_FONO_CLIENTE NOT NULL,
EMAIL VARCHAR2(255) CONSTRAINT NOTNULL_MAIL_CLIENTE NOT NULL,
FECHA_NACIMIENTO DATE CONSTRAINT NOTNULL_NACIMIENTO_CLIENTE NOT NULL,
SEXO CHAR(1) CONSTRAINT NOTNULL_SEXO_CLIENTE NOT NULL,
COD_CIUDAD NUMBER(3) CONSTRAINT NOTNULL_COD_CIUDAD NOT NULL
);

INSERT INTO CLIENTE 
VALUES('08798234-9','Luis','Garcia','Ramirez','Málaga 753',78452378,'lgarcia@gmail.com','12-APR-1977','M',1);


|        RUT | NOMBRE | APELLIDOP | APELLIDOM |  DIRECCION | TELEFONO |             EMAIL |     FECHA_NACIMIENTO | SEXO | COD_CIUDAD |
|------------|--------|-----------|-----------|------------|----------|-------------------|----------------------|------|------------|
| 08798234-9 |   Luis |    Garcia |   Ramirez | Málaga 753 | 78452378 | lgarcia@gmail.com | 1977-04-12T00:00:00Z |    M |          1 |

answered Dec 10 '17 at 02:12

Paul Maxwell

33,002
3
32
51

I do not understand why you do not enter the data in sql developer – Derezed Dec 10 '17 at 02:18
2

because I am using spare time, on a Sunday to answer questions for strangers and I'm not installing Oracle on my personal laptop for that. Is that sufficient explanation for you? The point of my answer is that it cannot be replicated so your question will not produce a definitive answer. The problem is at your end and we don't know how to help further. There is nothing wrong with that insert as I just proved. Perhaps you have a trigger? – Paul Maxwell Dec 10 '17 at 02:25
no, I do not have triggers – Derezed Dec 10 '17 at 02:45
if I put this data it is saved, but I do not know why the other one does not. `INSERT INTO CLIENTE VALUES('12378095-8','Antonio','Lopez','Tapia','Bravante 1345',97642378,'alt@gmail.com','18-JUL-1985','M',4);` – Derezed Dec 10 '17 at 02:47
1

“There is nothing wrong with that insert” - except it’s using a string instead of a date, so relying on implicit conversion and NLS settings; which are different in the OP’s environment to SQL Fiddle’s. It is kind of at the OP’s end, but it is reproducible *8-) – Alex Poole Dec 10 '17 at 09:23
@AlexPoole Excellent point. I should have picked that up. – Paul Maxwell Dec 10 '17 at 09:41

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

1 Answers1