Re: Dynamic SQL

A copy of this was sent to My Name <gxck_at_mindspring.com> (if that email address didn't require changing) On Wed, 09 Feb 2000 20:59:25 -0500, you wrote:

>Hello all:
>I am having problem with this dynamic sql because of the single quote
>around foo. Is there a way around it?
>
>select 'select * from '||table_name|| ' where sample_column = 'foo''
>from user_tables where table_name like '%BLAH';
>

ops$tkyte_at_8i> select 'select * from ' || table_name || ' where sample_column = ''foo'' '
  2 from user_tables where table_name like '%';

'SELECT*FROM'||TABLE_NAME||'WHERESAMPLE_COLUMN=''FOO'''

select * from A where sample_column = 'foo'
select * from ADDRESSES where sample_column = 'foo'
select * from B where sample_column = 'foo'
select * from BONUS where sample_column = 'foo'

use 2 quotes for 1 quote inside of a character string constant....

-- 
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