FreeLists / oracle-l / RE: Including special characters in query generation

["Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>]

Chirag,

 

I think you have unbalanced quotes:

 

select 'exec dbms_stats.gather_schema_stats (ownname=>' || ''''
||username||

       '''' || 

  ' estimate_percent=>dbms_stats.auto_sample_size,method_opt=>for all
columns size repeat,degree =>7)' 

from dba_users

 

 

When I pasted your query into Toad, it showed your number of quotes were
off.

I changed it to the above and it returned a correct string.

 

Make sure you skip the appropriate schemas like SYS, SYSTEM, OUTLN,
DBSNMP  etc.....

 

where username not in ('SYS','SYSTEM',etc.)

 

Good Luck!

 

Tom

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Chirag DBA
Sent: Friday, September 02, 2005 11:40 AM
To: Oracle-L Freelists; askdba@xxxxxxxxxxxxx
Subject: Including special characters in query generation

 

I want to generate a script for all users.

 

What is wrong here? There is one set parameter to set special characters
off.

 

select 'exec dbms_stats.gather_schema_stats
(ownname=>''''||username||'''||'
estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'||'''for all
columns size repeat''',degree =>7)' from dba_users; 

select 'exec dbms_stats.gather_schema_stats
(ownname=>'||'''||username||'''||'
estimate_percent=>dbms_stats.auto_sample_size,method_opt=>'||'''||for
all columns size repeat||''',degree =>7)' from dba_users; 
 

can anyone tell me what is missing here ? 

 

Regards - Chirag