Scape single quote in dynamic query Oracle I have a field street name that would accept names like : O'connor but I have tested with replace function that works fine, but not sure if this is going to cause an exception with numeric values(int). Since my query is built in a v_msql variable dynamically I want to set the value to replace the single quote with two single quotes. 

select   replace( ISR.STREET_NAME1 , '''','''''')
           || chr(3) || isr.agency_code || chr(3) || ''  
           from isr, isr_udf where isr.isr_no = isr_udf.isr_no and isr.isr_no = 'XXX-20160718-00022'   ; 


So I am wondering if Replace would handle the scape correctly. I tested it and shows no problem. Are there other ways to  scape a single quote on street names?
You can use another method to quote strings in Oracle which makes things a lot clearer.
Example:

x := q'[This is John's address]';

y := q'[He said "What's your name ?"]';


You can have as many quote marks as you like so long as they are between the '[]'.
Your code may look something like this

SELECT REPLACE(ISR.STREET_NAME1, q'[']',q'['']')
       || CHR(3) || isr.agency_code || CHR(3) || ''  
  FROM isr, isr_udf
 WHERE isr.isr_no = isr_udf.isr_no
   AND isr.isr_no = 'XXX-20160718-00022'


Link: https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/fundamentals.htm#CBJJDDCG