Search code examples
sqlstringoracle-databasesql-insertclob

insert string which includes quotes in oracle

How can I insert string which includes quotes in oracle? my code is

INSERT INTO TIZ_VADF_TL_MODELS (name)
VALUES ('xxx'test'yy');

if I use 

INSERT INTO TIZ_VADF_TL_MODELS (name)
VALUES ("xxx'test'yy");

I get identifier is too long error because xxx'test'yy is clob.

how can I do that?

thx.

Solution

  • You can also use the 'alternative quoting mechanism' syntax:

    INSERT INTO TIZ_VADF_TL_MODELS (name)
VALUES (q'[xxx'test'yy]');

    The pair of characters immediately inside the first set of quotes, [] in this case, delimit the quoted text; single quotes within those do not have to be escaped. Of course, you can't then have ]' within the string itself, but you can pick your own delimiters so that can be avoided if it's going to be an issue; ] on its own would still be OK.

    This can be simpler than making sure single quotes are escaped, which can get a bit messy, or at least hard to read and debug.

    SQL Fiddle.