CODEWITHSUNDEEP
sqloracle-databasesqlplus
Jose Partida
Jose Partida

Reputation: 23

Invalid Character during my SQL Plus Insert Statement

I made a table for my SQL class, however when I try to insert data, it's giving me an error on Borough char input : 

CREATE TABLE Child (
childID VARCHAR (5) PRIMARY KEY,
firstName CHAR (10),
lastName CHAR (15),
dateOfBirth DATE,
street VARCHAR (20),
city CHAR (10),
ZIP VARCHAR (5),
phone VARCHAR (15),
borough CHAR (15)
);

INSERT INTO Child
VALUES (‘C001’, John, Wick, 2017-02-16 , ‘123 Jay Street’, New York, ‘11201’, ‘212-777- 
6677’, Brooklyn);
           *
"Error at Line 2, invalid Character ORA-00911"

Specifically it's telling me "Error on Line 2, invalid Character" on the letter k in Brooklyn. I tried with a different combination of quotations however it still gave me the same error, this time on the number 3 in the phone number 

INSERT INTO Child
VALUES (‘C002’, ‘Wayne’, ‘Brady’, ‘2017-02- 
16’, ’24 Atlantic Ave’, ‘New York’, ‘11201’, 
‘212-888-2345’, ‘Brooklyn’);
          *
"Error at Line 2, invalid Character ORA-00911

Any help would be appreciated !

Upvotes: 1

Views: 376

Answers (2)

The Impaler
The Impaler

Reputation: 48875

Those single quotes you are using are weird. Use standard single quotes (') instead, as in:

INSERT INTO Child
(childID, firstName, lastName, dateOfBirth, street, city, ZIP, phone, borough)
VALUES ('C001', 'John', 'Wick', to_date('2017-02-16','YYYY-MM-DD'), '123 Jay Street', 'New York', '11201', '212-777-6677', 'Brooklyn');

In Oracle you should also:

  • Avoid using the VARCHAR type. Use VARCHAR2 instead.
  • Name all columns in the INSERT.

Upvotes: 6

bruceskyaus
bruceskyaus

Reputation: 783

"Curly" quotes around the text fields are caused by using an editor like Microsoft Word, which are designed for documents being read by human beings, not SQL queries. The quotes must be the ASCII ' single quote character. Use single quotes for all text values in your INSERT statement, as answered by @The Impaler.

To avoid this problem in future, use a dedicated text editor with good syntax highlighting for .sql files, such as Notepad++ for Windows or Sublime Text for Mac. Or you can just do all of your editing in the SQL client, such as Oracle SQL Developer.

Upvotes: 3

Related Questions