Re: HELP: SQL+ : How to use & in strings in PL/SQL scripts

A copy of this was sent to Howard Bryden <100251.1711_at_CompuServe.COM> (if that email address didn't require changing) On 21 Jan 2000 05:42:11 GMT, you wrote:

>
> << ... or use chr(38) in place of & in the source. (eg: instead
> of 'Ben & Jerry', code 'Ben ' || chr(38) || ' Jerry'.... >>
>

> This should do just as well:
>

> 'Ben ' || '&' || ' Jerry'
>

> At least it does under 7.3.4.

neat -- did not know that.

>

> BTW, doyou know of a way where I can pass a DEFINEd string into
> the PL/SQL environment where that string could include single
> quotes? E.g.
>

> define x = "&1"
> declare xx varchar2(80);
> begin
> xx := 'select whatever from table &x';
>

> fails if &1 was something like "WHERE colname = 'value'" because
> of the quotes.
>

>

it is a trick question because the string &x is being passed to the plsql environment perfectly -- the problem is that the string needs to have 2 quotes for every single quote -- as all character string constants do.

You need to have &1 set to "WHERE colname = ''value''" and then it'll work.

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation