Re: Dynamic SQL

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 10 Feb 2000 07:32:07 -0500
Message-ID: <2ib5ascrsjcjenqbi98e7cmu5cnocn4c9r@4ax.com>

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

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> 

ops$tkyte_at_8i>
ops$tkyte_at_8i> SELECT "SELECT * FROM "||table_name||

  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