CODEWITHSUNDEEP
sqloracle-databasesql-insert
john
john

Reputation: 439

"column not allowed here" error in INSERT statement

I created this table called LOCATION by doing this:

CREATE TABLE LOCATION(
POSTCODE VARCHAR(10) PRIMARY KEY,
STREET_NAME VARCHAR(20),
CITY VARCHAR(20));

and when I try to add some date within the table it doesn’t work saying there is an error

INSERT INTO LOCATION VALUES(PQ95VM,'HAPPY_STREET','FRANCE');

error is saying

column not allowed here

Upvotes: 42

Views: 257852

Answers (14)

Nilay Thakkar
Nilay Thakkar

Reputation: 11

In SQL Plus,we have to use single quotes('') instead of double quotes("").

Example:

insert into emp values(1,"Nilay"); // This is wrong

insert into emp values(1,'Nilay'); // This is right

Upvotes: 1

Peter Chaula
Peter Chaula

Reputation: 3731

I made the error of using the wrong quotation marks. I used double quotes as string delimiters in my value list instead of single quotes. Double quotes in Oracle are used as delimiters in column identifiers unlike in MySQL where they are used to enclose string literals.

INSERT INTO MYUSERS VALUES( 'Name', "Surname");  // Wrong

INSERT INTO MYUSERS VALUES( 'Name', 'Surname'); // Correct, Surname must be in single quotes

Upvotes: 8

RoadVampire
RoadVampire

Reputation: 99

If PQ95VM is supposed to be a literal string, then use 'PQ95VM'. If it is a variable, make sure it is declared.

Upvotes: 0

Gabriel Arghire
Gabriel Arghire

Reputation: 2370

Convert double quotes (" ") to single quotes (' ')

Example

If you have something like this:

INSERT INTO clients (id, email, country)
VALUES (1, "[email protected]", "USA");

Convert it to this:

INSERT INTO clients (id, email, country)
VALUES (1, '[email protected]', 'USA');

Upvotes: 3

Tanu
Tanu

Reputation: 11

I too got the same error while working with insert command. When i replaced double quotes with single quotes it worked fine

Upvotes: 0

Shweta Tamrakar
Shweta Tamrakar

Reputation: 1

While inserting the data, we have to used character string delimiter (' '). And, you missed it (' ') while inserting values which is the reason of your error message. The correction of code is given below:

INSERT INTO LOCATION VALUES(PQ95VM,'HAPPY_STREET','FRANCE');

Upvotes: 0

Shravya Mutyapu
Shravya Mutyapu

Reputation: 318

Try using varchar2 instead of varchar and use this :

INSERT INTO LOCATION VALUES('PQ95VM','HAPPY_STREET','FRANCE');

Upvotes: 0

Hiruni K
Hiruni K

Reputation: 179

INSERT INTO LOCATION VALUES(PQ95VM,'HAPPY_STREET','FRANCE');

the above mentioned code is not correct because your first parameter POSTCODE is of type VARCHAR(10). you should have used ' '.

try INSERT INTO LOCATION VALUES('PQ95VM','HAPPY_STREET','FRANCE');

Upvotes: 4

Avinash Khadsan
Avinash Khadsan

Reputation: 497

Scanner sc = new Scanner(System.in); 
String name = sc.nextLine();
String surname = sc.nextLine();
Statement statement = connection.createStatement();
String query = "INSERT INTO STUDENT VALUES("+'"name"'+","+'"surname"'+")";
statement.executeQuery();

Do not miss to add '"----"' when concat the string.

Upvotes: 0

Vijayaragavan
Vijayaragavan

Reputation: 969

Some time, While executing insert query, we are facing:

Column not allowed here

error. Because of quote might missing in the string parameters. Add quote in the string params and try to execute.

Try this:

INSERT INTO LOCATION VALUES('PQ95VM','HAPPY_STREET','FRANCE');

or 

INSERT INTO LOCATION (ID, FIRST_NAME, LAST_NAME) VALUES('PQ95VM','HAPPY_STREET','FRANCE');

http://www.drtuts.com/oracle-error-column-not-allowed-here/

Upvotes: 5

this.shivi
this.shivi

Reputation: 299

What you missed is " " in postcode because it is a varchar.

There are two ways of inserting.

When you created a table Table created. and you add a row just after creating it, you can use the following method. 

INSERT INTO table_name
VALUES (value1,value2,value3,...);

1 row created.

You've added so many tables, or it is saved and you are reopening it, you need to mention the table's column name too or else it will display the same error. 

ERROR at line 2:
ORA-00984: column not allowed here


INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

1 row created.

Upvotes: 2

BlueConga
BlueConga

Reputation: 917

Like Scaffman said - You are missing quotes. Always when you are passing a value to varchar2 use quotes

INSERT INTO LOCATION VALUES('PQ95VM','HAPPY_STREET','FRANCE');

So one (') starts the string and the second (') closes it.

But if you want to add a quote symbol into a string for example:

My father told me: 'you have to be brave, son'.

You have to use a triple quote symbol like:

'My father told me: ''you have to be brave, son''.'

*adding quote method can vary on different db engines

Upvotes: 3

user2431326
user2431326

Reputation: 29

This error creeps in if we make some spelling mistake in entering the variable name. Like in stored proc, I have the variable name x and in my insert statement I am using

insert into tablename values(y);

It will throw an error column not allowed here.

Upvotes: 0

skaffman
skaffman

Reputation: 403601

You're missing quotes around the first value, it should be

INSERT INTO LOCATION VALUES('PQ95VM', 'HAPPY_STREET', 'FRANCE');

Incidentally, you'd be well-advised to specify the column names explicitly in the INSERT, for reasons of readability, maintainability and robustness, i.e.

INSERT INTO LOCATION (POSTCODE, STREET_NAME, CITY) VALUES ('PQ95VM', 'HAPPY_STREET', 'FRANCE');

Upvotes: 67

Related Questions