Inserting a string with double quotes into a table

Question

I'm using Oracle 10g and I'm having a problem inserting a string with double quotes into a table. This is my statement

INSERT INTO USERS (ID, NAME, USERNAME) VALUES (NULL, "tes", "hello");

The query above fails with the error "Oracle column not allowed here".

If I change double quotes to single quotes, as below the statement is successful.

INSERT INTO USERS (ID, NAME, USERNAME) VALUES (NULL, 'tes', 'hello');

But, I want to insert the double quotes into the table.

Is it possible to have double quote in strings in an insert statement? I don't want to use REPLACE() because my query is automatically generated from an array.

Answer 1

It is possible. In Oracle, you quote string literals using single quotes.

If you want to insert test into the database then you must quote that as 'test'.

INSERT INTO USERS (NAME) VALUES ('test');

If you want to insert "test" into the database then you must quote that as '"test"'.

INSERT INTO USERS (NAME) VALUES ('"test"');