I recently encountered a requirement at work, which requires updating a field "flag" of all tables in the Oracle database to "I". The statement is:

update table_name set flag = 'I'

"I" is used as a string, so single quotes are required for I in the statement.

Since there are many tables in the database, I don’t want to write statements one by one. I hope to be able to directly generate all statements through sql statements, so I wrote the following sql:

select 'update ' || table_name || ' set flag = 'I'' || ';' from user_tables

When running, "Error: ORA-00923: The required FROM keyword was not found". The error location is at the second single quote position of 'I'. Because of the single quote pairing relationship, I It is not a string, so an error is reported. So what should I do if I want to display a single quote?

Tried the statement:

select ''' from dual

An error will also be reported. It turns out that "'" is used as a keyword. If you need to display "'", you need to use an escape character, just like java's "\"

And Oracle's escape character is. Single quotation mark "'", so the sql should be written like this:

select '''' from dual

##How to understand it? Treat the second single quotation mark "'" as "\" Okay, so the sql I need is:

select 'update ' || table_name || ' set flag = ''I''' || ';' from user_tables

That’s it!

Summary

The above is about This is all about Oracle escape characters. I hope the content of this article can be of some help to everyone's study or work. If you have any questions, you can leave a message to communicate.

More detailed introduction to escape characters in Oracle. Please pay attention to PHP Chinese website

for articles!