Invalid number in SQL Developer to test queries

Question

I am trying to test my queries based on this table:

--Create Person Table
CREATE TABLE Person
(
    Person_ID NUMBER(10) PRIMARY KEY,
    First_Name NUMBER(15) NULL,
    Last_Name VARCHAR2(15) NULL,
    Middle_Name VARCHAR2(15) NULL,
    Street_Address VARCHAR(35) NULL,
    City    VARCHAR2(10) NULL,
    State   VARCHAR2(2) NULL,
    Zipcode VARCHAR2(5) NULL, 
    Country CHAR(2) NOT NULL, 
    Birth_Date DATE NOT NULL,
    Gender CHAR(1) NOT NULL,
    Phone VARCHAR2(10) NULL,
    Email VARCHAR2(25) NULL,
    Is_Patient CHAR(1) NULL,
    Is_Physician CHAR(1) NULL,
    Is_Employee CHAR(1) NULL,
    Is_Volunteer CHAR(1) NULL 
);

And this is what I input:

INSERT INTO PERSON (Person_ID, First_Name, Last_Name, Middle_Name, Street_Address,
City, State, Zipcode, Country, Birth_Date, Gender, Phone,
Email, Is_Patient, Is_Physician, Is_Employee, Is_Volunteer)

VALUES
    (1234, 'Jill', 'Palmer', 'Wendy', '123 Apple Court',
     'Bronx', 'NY', '10472', 'USA', date '1956-09-15', 'F', '9178546215',
     'heygirl5@gmail.com', 'Y', 'N', 'N','N');

VALUES
    (2345, 'Charles', 'Connor', 'Vanny', '87455 Lemon Drive',
     'Queens', 'NY', '12047', 'USA', date '1953-10-08', 'M', '9178546200',
     'sugahsugah@gmail.com', 'N', 'N', 'Y','N');

VALUES
    (3456, 'Chaniece', 'Boone', 'Pia', '94102 Grape Meaows',
     'Brooklyn', 'NY', '12047', 'USA', date '1983-03-31', 'F', '3479845102',
     'byenow@gmail.com', 'N', 'Y', 'N','N');

VALUES
    (4567, 'James', 'Boone', 'Wayck', '2010 Orange Place',
     'Manhattan', 'NY', '10026', 'USA', date '1981-08-03', 'M', '7187884411',
     'howyoudoin@gmail.com', 'Y', 'N', 'N','N');

VALUES
    (5678, 'Charlton', 'Connor', 'Ethan', '94165 Mango Lane',
     'Staten Island', 'NY', '30124', 'USA', date '1988-12-10', 'M', '6469820145',
     'youcrazyboy@gmail.com', 'Y', 'N', 'N','Y');

SELECT *
FROM PERSON;

I am getting an error of invalid number and I don't see why. Nothing is coming up on my test also.

Welcome to StackOverflow: if you post code, XML or data samples, ***please*** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! — marc_s, Apr 05 '20 at 05:39
`insert into values ... values ...` is invalid to begin with. You can only have a single `values` clause and that can only insert a single row in Oracle. — , Apr 15 '20 at 12:43

William Robertson · Answer 1 · 2020-04-05T14:53:23.103

You defined First_Name as NUMBER(15). You probably meant VARCHAR2(15).

Also, you have a mix of CHAR, VARCHAR and VARCHAR2. These should all be VARCHAR2. (CHAR will seem to work, but it just causes problems for no added benefit).

create table person
( person_id         number(10) primary key
, first_name        varchar2(15)  -- Not number
, last_name         varchar2(15)
, middle_name       varchar2(15)
, street_address    varchar2(35)
, city              varchar2(30)  -- Increased from 10
, state             varchar2(2)
, zipcode           varchar2(5)
, country           varchar2(3) not null  -- increased size to 3 to fit 'USA'
, birth_date        date not null
, gender            varchar2(1)
, phone             varchar2(10)
, email             varchar2(25)
, is_patient        varchar2(1)
, is_physician      varchar2(1)
, is_employee       varchar2(1)
, is_volunteer      varchar2(1)
);

insert into person
( person_id
, first_name, last_name, middle_name
, street_address, city, state, zipcode, country
, birth_date
, gender, phone, email
, is_patient, is_physician, is_employee, is_volunteer )
values
( 1234  -- Not '1234'
, 'Jill', 'Palmer', 'Wendy'
, '123 Apple Court', 'Bronx', 'NY', '10472', 'USA'
, to_date('09/15/1956', 'MM/DD/YYYY')  -- Corrected format mask (alternatively: date '1956-09-15')
, 'F'
, '9178546215'
, 'heygirl5@gmail.com'
, 'Y', 'N', 'N', 'N'  -- Not 'YES'/'NO' because the columns are 1 character
);

Maybe some more of the columns should be declared NOT NULL. Is it really OK for a person not to have a name or address or anything else besides a country and birth date? (You don't need to specify NULL for a nullable column, and doing so makes it harder to tell which ones are mandatory.)

Probably most of the name columns should be longer.

Ideally Y/N columns should be mandatory and enforced by check constraints, e.g.

is_patient  varchar2(1) not null  constraint is_patient_yn_chk check (is_patient in ('Y','N'))

In addition to the other comments (spot on, all of them) if patient/physician and employee/volunteer are mutually exclusive, they should be reduced to a single 'status' column . perhaps PATIENT_PHYS_STATUS, and EMP_VOL_STATUS, each with appropriate values to indicate which, and a check constraint to enforce. — EdStevens, Apr 05 '20 at 13:25
@William Robertson I already created the table with Number and Char unfortunately. Ill try it the way that Tim Biegeleisen said to see if that will work since the table is already created and Schema made — Dominique Connor, Apr 06 '20 at 02:43
@William Robertson that makes a lot of sense now lol wish you were here a week ago when I was creating the table but now that the table is created I can not change it. Is there a way I can fix it with what I have. I am still getting invalid number. — Dominique Connor, Apr 06 '20 at 03:22
@DominiqueConnor: why can't you change it? I doubt your application is already in production, so you can still run an ALTER TABLE to fix the incorrectly chosen data types (and if it **was** in production, fixing that is even more important) — , Apr 15 '20 at 12:44

score 0 · Answer 2 · answered Apr 05 '20 at 04:28

The exact error you are seeing is probably being caused by that you are trying to insert a string value into the Person_ID column, which you have defined as numeric. Beyond this, you should always explicitly list the columns for the insert:

INSERT INTO PERSON (Person_ID, First_Name, Last_Name, Middle_Name, Street_Address,
    City, State, Zipcode, Country, Birth_Date, Gender, Phone,
    Email, Is_Patient, Is_Physician, Is_Employee, Is_Volunteer)
VALUES
    (1234, 'Jill', 'Palmer', 'Wendy', '123 Apple Court',
     'Bronx', 'NY', '10472', 'USA', date '1956-09-15', 'F', '9178546215',
     'heygirl5@gmail.com', 'Y', 'N', 'N','N');

Also note that for the Is_xxx columns, you should only be inserting a 'Y' or 'N' value, as you have defined these columns to be CHAR(1).

Invalid number in SQL Developer to test queries

2 Answers2