Suppose I have a column value as aaa'gh it will throw error in oracle saying sql command not properly ended.
My question is if I don't know how many ' are in my value, how can I escape them safely.
解决方案
The best way is to use the quoting string literal technique. The syntax is q'[...]', where the "[" and "]" characters can be any of the following as long as they do not already appear in the string.
!
[ ]
{ }
( )
< >
You don't have to worry about the single-quotation marks within the string.
Suppose i have a column value as aaa'gh
So you could simply write the SQL as,
SELECT q'[aaa'gh]' FROM DUAL;
It saves a lot of time for developers. Gone are those days when we(developers) used to verify the dynamic sql using dbms_output in development DB, just to make sure things are at place before moving into production.