[sql] "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

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');

Try using varchar2 instead of varchar and use this :

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

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.

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

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');

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.

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');

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.

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/