Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SQL
A copy of this was sent to "Billy Verreynne" <vslabs_at_onwe.co.za>
(if that email address didn't require changing)
On Thu, 10 Feb 2000 08:56:01 +0200, you wrote:
>[i personally hate these stupid crossposting question - so crosspostings to
>other newsgroup have been nuked in this posting]
>
>My Name wrote in message <38A21B7D.C4C673E6_at_mindspring.com>...
>>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';
>
>
>
>SELECT
> "SELECT * FROM "||table_name||
> "WHERE sample_column = 'foo'"
>FROM user_tables
>WHERE table_name LIKE '%BLAH';
>
that answer won't work:
ops$tkyte_at_8i> SELECT
2 "SELECT * FROM "||table_name||
3 "WHERE sample_column = 'foo'"
4 FROM user_tables
5 WHERE table_name LIKE '%BLAH';
"WHERE sample_column = 'foo'"
*
ERROR at line 3:
ORA-00904: invalid column name
the use of DOUBLE quotes in ANSI sql indicates you are quoting an identifier. The above query says to look for a column names "SELECT * FROM ", table_name, and "WHERE sample_column = 'foo'" in the table/view user_tables. You would have had to of:
ps$tkyte_at_8i> create or replace view user_tables
2 as
3 select 'SELECT * FROM ' "SELECT * FROM ",
4 table_name, 5 ' WHERE sample_column = ''foo''' "WHERE sample_column = 'foo'"6 from sys.user_tables
View created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> desc user_tables
Name Null? Type ----------------------------------------------------- -------- ------------------------------------ SELECT * FROM CHAR(14) TABLE_NAME NOT NULL VARCHAR2(30) WHERE sample_column = 'foo' CHAR(28) ops$tkyte_at_8i>
2 "WHERE sample_column = 'foo'" 3 FROM user_tables 4 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'....
although I wouldn't suggest it for obvious reasons :)
You should:
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....
>
>Some suggestions though. I do not like to reply to postings where the writer
>is called "My Name". Please have the courtesy to use your real name
>(preferably) or a nickname. I also detest cross postings. Decide which
>newsgroup is the best to answer your question and post it there.
>Crosspostings are simply rude and (IMO) very arrogant.
>
>regards,
>Billy
>
>
--
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 Received on Thu Feb 10 2000 - 06:32:07 CST