Re: using EXECUTE IMMEDIATE to run DBMS_STATS.GATHER_SCHEMA_STATS

From: joel garry <joel-garry_at_home.com>
Date: Thu, 20 Sep 2012 09:02:17 -0700 (PDT)
Message-ID: <ed872e35-bf76-462d-8f70-ebcfaddbb9aa_at_wz4g2000pbc.googlegroups.com>

On Sep 20, 8:20�am, abani.naren..._at_gmail.com wrote:
> On Friday, October 31, 2008 5:23:34 PM UTC+5:30, Kevin S wrote:
> > When I run the block below, in oracle 10.2.0.3.0, I get the error
> > shown after. Is this a problem with my coding or is it just not
> > possible to run this commad in pl/sql?
>
> > Thanks
>
> > DECLARE
> > � � v_dbms_stats � �VARCHAR2(200):= 'EXEC
> > DBMS_STATS.GATHER_SCHEMA_STATS( ownname �=> ''LOAD'',
> > estimate_percent => 100, cascade => �TRUE,options => ''GATHER
> > AUTO'');';
> > BEGIN
> > � �EXECUTE IMMEDIATE �v_dbms_stats;
> > END;
>
> > ERROR at line 1:
> > ORA-00900: invalid SQL statement
> > ORA-06512: at line 4
>
> Hi I had a similar requirement which I resolved.
>
> SET SERVEROUTPUT ON;
> DECLARE
> TAB VARCHAR2(30):='EMP';
> BEGIN
> EXECUTE IMMEDIATE 'BEGIN DBMS_STATS.GATHER_TABLE_STATS (ownname => ''SCOTT'',tabname => '''||TAB||''' , estimate_percent => 10, degree => 4, granularity => ''ALL'', cascade => TRUE);
> � END;';
> END;
>
> This should help! Thanks
>
> Abanikant

When using the new google to read this group, please be aware that you can reply to very old threads. In general you don't want to do that, it makes you look kind of foolish, so watch the dates of what you are replying to. It doesn't help.

jg

--
_at_home.com is bogus.
http://www.utsandiego.com/news/2012/sep/19/prosecutors-accuse-21-people-financial-aid-fraud/

Received on Thu Sep 20 2012 - 11:02:17 CDT