RE: Including special characters in query generation

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Chirag DBA Sent: Friday, September 02, 2005 11:40 AM To: Oracle-L Freelists; askdba_at_freelists.org 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

--
http://www.freelists.org/webpage/oracle-l