Re: Problem with single quote character

Chris L. wrote:
> Hi,
>
> The problem: Building dynamic SQL sometimes results in statements like
> this:
>
> execute immediate
> 'update owner.customers set city=''Unknown'', country=''Cote D'Ivoire''
> where cust_id=37'
> ;
>
> This is a problem, because the quote in [Cote D'Ivoire] renders the sql
> statement invalid.
>
> Easily fixed, duplicating the quote as below:
>
> 'update owner.customers set city=''Unknown'', country=''Cote
> D''Ivoire'' where cust_id=37'
>
> My question: What other characters could "break" the sql? Maybe [:],
> [\], [&], [%]? Is there a complete list somewhere?
>
> Furthermore, I remember seeing on documentation an option to make other
> characters play the role of the single quote but no matter what I
> search terms I use, I can't seem to find it.
>
> Oracle 9i 9.2.0.4.0
>
> Thanks all in advance.
>

My first opinion is that coding UPDATE statements dynamically is often bad coding. If you just code the UPDATE statement in a PL/SQL block and put the values in variables, then you won't run into this issue.

If you insist on using dynamic SQL in this manner, then you'll have to pre-parse the values. So if you have "Cote D'Ivoire" in a variable, then run it through REPLACE first to change the single quote to two single quotes.

About the only other character I can think of off the top of my head that would interfere in your EXEC IMMED statement is the semicolon, which is a statement terminator.

HTH,
Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown