Re: how to put quotes in dynamic sql?

You should be able to use a set of single quotes there:

exec ('create table table_name as select * from other_table where field=''val''');

So each quote in the string becomes a set of singles, and the whole string is enclosed in quotes.
For an example, see the ANYSTRING example in Appendix A of ORACLE SQL & PL/SQL
Annotated Archives by Loney & Carmichael, ObDisclaimer.

hth.
Kevin Loney
http://www.kevinloney.com

ewong74_at_netscape.net wrote in message <7atho3$k9e$1_at_nnrp1.dejanews.com>...
>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!
>
Received on Tue Feb 23 1999 - 15:39:36 CST