Re: ' in dynamic sql

Allan Martin doodled thusly:

>Thanks very much for getting back to me.
>
>I was working along those lines, and it seems to be working.... apart from
>one thing. The data type I'm working with is a LONG, it complains on
>inconsistent datatypes if I try that.
>
>Any advice?
>

If it is a LONG, you must be using some form of pre-compiler or language to handle that. As opposed to an interactive interface like SQL*Plus. So, use whatever is available in that language to replace the single quote by a double single quote, BEFORE you send the value down to build the dynamic SQL.

Someone else sugested bind variables. If your interface allows use of bind variables for LONG values, then that is also a good idea: any string contained inside a bind variable is passed as is, without any need for quote replacement.

Don't forget that the "double single quote" construct for inserting quotes in a string is a *convention* of some interfaces like SQL*Plus. As opposed to a feature of ORACLE.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Fri Dec 07 2001 - 05:49:56 CST