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');
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');
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
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
Example
If you have something like this:
INSERT INTO clients (id, email, country)
VALUES (1, "john@gmail.com", "USA");
Convert it to this:
INSERT INTO clients (id, email, country)
VALUES (1, 'john@gmail.com', 'USA');
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.
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.
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.
Try using varchar2 instead of varchar and use this :
INSERT INTO LOCATION VALUES('PQ95VM','HAPPY_STREET','FRANCE');
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');
I too got the same error while working with insert command. When i replaced double quotes with single quotes it worked fine
If PQ95VM is supposed to be a literal string, then use 'PQ95VM'. If it is a variable, make sure it is declared.
