Re: how to put quotes in dynamic sql?

Hi Ed,

use dobble-quotes or chr(39):

select 'Text ''in quotes''' from dual;
or
select 'Text '||chr(39)||'in quotes'||chr(39) from dual;

returns:

Text 'in quotes'

this works also in stored procedures

hope this helps

        Reinhard

ewong74_at_netscape.net schrieb:
>
> I have a pl/sql block that pass a DDL as a string into an other procedure to
> execute the DDL statements using dynamic sql. The dynamic sql procedure is
> called 'exec':
> exec('DROP TABLE table_name');
>
> I got a problem when I come across a DDL which has a string in the where
> clause. The multiple quotes generate an error: exec('CREATE TABLE table_name
> as SELECT * from other_table where field_name 'hello''); ^^
>
> Any ideas?
> Thanx in advance!
>
> Ed
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Tue Feb 23 1999 - 04:27:11 CST