1

Was hoping that this question

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

Would provide an answer, however it seems like this scenario is different than what any of the answers cover.

I am making this table 

CREATE TABLE Patients 
(PatientID NUMBER(5) PRIMARY KEY, 
FirstName CHAR(20) NOT NULL, 
LastName CHAR(20) NOT NULL, 
ZipCode NUMBER(5) NOT NULL,
City CHAR(20) NOT NULL , 
State CHAR(20) NOT NULL, 
DOB DATE NOT NULL,
EmailAddress CHAR(30) NOT NULL, 
PhoneNumber NUMBER(11) NOT NULL, 
Comments VARCHAR(200));

and this sequence

CREATE SEQUENCE Pat_seq
START WITH 1
INCREMENT BY 1
MINVALUE 0
NOCYCLE;

and then filling out the table with an insert of this format

INSERT INTO Patients VALUES 
(Pat_seq.nextval, 
'John', 
'Smith', 
'11277',
'Southampton',
'New York',
'10-Jan-1980',
'John.Smith@gmail.com',
'5555555555',
'No Comments');

This worked successfully in a SQL Plus Oracle server last night. I only have access to this server on my schools campus however, so to practice at home I requested a workspace through the Apex Oracle website.

When I attempt to run the insert statement on the Apex Workspace, I recieve the error in the title.

ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "SYS.DBMS_SQL", line 1721


1. INSERT INTO Patients VALUES 
2. (Pat_seq.nextval, 
3. 'John',

From what I can tell, the error is being triggered because the sequence is conflicting with the PatientID being a Number datatype. My question is why?

This is the correct way to insert a sequence, no? It works fine in SQL Plus, so is this just a flaw of the Apex Oracle workspace?

EDIT:

To clarify: - I am using the Oracle APEX tool, version 19.2. I'm having trouble finding the documentation specifying the exact version of Oracle this uses.

  • The exact same code worked last night using SQL Plus.
SchrodingersStat
  • 291
  • 3
  • 19
  • Be aware that it is highly likely that code that works correctly in MySQL will not work correctly in Oracle (and vice versa). If you are just learning MySQL's SQL dialect, you're probably better off using one of the various fiddles that will give you a MySQL environment (i.e. https://www.db-fiddle.com/) to practice with rather than trying to use Oracle at home and MySQL at school. – Justin Cave Nov 12 '19 at 17:03
  • Wow, huge mistake on my part. SQL Plus, not MySQL. It's an Oracle database that we run through SQL Plus. – SchrodingersStat Nov 12 '19 at 17:08
  • 1
    Avoid using CHAR as it pads strings with spaces. Use VARCHAR2 instead. – Jeffrey Kemp Nov 13 '19 at 03:47

2 Answers2

4

It could be the DATE value not matching the default format. You should explicitly convert the string to a DATE using the correct format:

TO_DATE ('10-Jan-1980', 'DD-Mon-YYYY')

Alternatively, use the ISO date literal format:

DATE '1980-01-10'

Also, since you have not listed the columns of the table in your INSERT statement, it is possible that you are providing the values in the wrong order. An INSERT statement should be written like:

INSERT INTO Patients 
  (PatientID, 
   FirstName, 
   ...)
VALUES
  (Pat_seq.nextval, 
   'John',
  ...);
Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 1
    Thanks for the tip on the insert statement. Was aware of that format but have avoided it because it felt clunky, although I guess explicit is always better practice. Gonna switch going forward. Checking this answer now also. – SchrodingersStat Nov 12 '19 at 16:28
  • Does that TO_DATE replace DATE when I'm creating the table, or would that be in the INSERT? – SchrodingersStat Nov 12 '19 at 16:30
  • No, TO_DATE is a function, DATE is a datatype (table definition). – Tony Andrews Nov 12 '19 at 16:37
  • So I need to make the date a VARCHAR datatype when I create the table, INSERT the data, and then run a SELECT query with the TO_DATE function? – SchrodingersStat Nov 12 '19 at 16:48
  • No, create the table with a DATE datatype. Insert the value using TO_DATE so that what you are inserting is a DATE not a text string. Without the TO_DATE function Oracle will perform an "implicit conversion" of the string to a DATE, which may not use the same format as you have. – Tony Andrews Nov 12 '19 at 16:50
  • Worked. Thanks for the help, and the patience! – SchrodingersStat Nov 12 '19 at 16:58
0

What version of Oracle are you running?

We are on 12.1.0.2.0, and the syntax you used worked fine for me.

CREATE TABLE my_test (id_number VARCHAR2(10), xsequence NUMBER(5));

CREATE SEQUENCE my_test_seq MINVALUE 0 MAXVALUE 9999999999999999999999999999 START WITH 1 INCREMENT BY 1 CACHE 20;

INSERT INTO my_test VALUES ('0000000001', my_test_seq.nextval); COMMIT;

SELECT * FROM my_test;

Results:

ID_NUMBER XSEQUENCE

0000000001 1

StewS2
  • 401
  • 4
  • 10
  • This is in Oracle APEX 19.2. To be clear, the exact same code worked for me last night but using MySQL, which I don't have access to at home. Part of my concern is that this tool is not adequate to practice with. – SchrodingersStat Nov 12 '19 at 16:34
  • Given that the ultimate problem was with the date in your INSERT statement, I'd say the Apex tool worked as it should have. My original thought was, if this is a pure Oracle (not Apex) issue, why'd they tag this "oracle-apex"? Glad you got it working. – StewS2 Nov 12 '19 at 18:48
  • Well, the original INSERT Statement worked fine in SQL Plus, but not in Oracle APEX. It technically *was* an issue with APEX, as it was an issue with the date format that APEX uses not being the same as the format I had originally used. – SchrodingersStat Nov 12 '19 at 20:11