dean wrote:
> Brian Peasland wrote:
>> 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
>
> I think underscore can have different meanings - such as wildcard
> single character, so you have to be careful with that.
>
> Dean
They do but you can escape them. There is a demo of this in Morgan's
Library at www.psoug.org under Wildcards. Scroll down to the "Querying
Records Containing Wildcards" demo.
--
Daniel Morgan
University of Washington
Puget Sound Oracle Users Group
Received on Fri Oct 06 2006 - 10:58:58 CDT
