Escaping special characters in SQL

Question

Is there an easy way in Oracle to escape special characters in a SQL statement? (i.e. %, &, ') I saw this link in regard to manually escaping characters, but I thought Oracle may have provided an easier way to do so.

Note: I'm generating dynamic SQL select statements through an ORM.

Answer 1

If using bind variables and ORM, embedded single quotes and ampersands should be handed automatically; those are special characters in SQL*Plus or SQL*Developer.

To use LIKE where looking for the literal characters % and _ (not their multi- and single-character wildcard versions), you'd use the escape clause of the like condition:

select * from my_table where some_text like '/%%' escape '/';

will only return the rows where some_text begins with a percent sign.

Answer 2

It seems you're looking for something like the command SET DEFINE OFF, which you can run and it affects the whole SQL session. This command, however, only prevents Oracle from giving special meaning to the ampersand character. It doesn't affect other special characters such as the single quote.

A couple of links to additional information regarding escaping characters follow:

https://forums.oracle.com/forums/thread.jspa?threadID=2256637

http://docs.oracle.com/cd/B10501_01/text.920/a96518/cqspcl.htm