Re: using EXECUTE IMMEDIATE to run DBMS_STATS.GATHER_SCHEMA_STATS
Date: Fri, 31 Oct 2008 10:52:28 -0700 (PDT)
Message-ID: <a80e1fb9-d54b-4b24-af28-f827d091de5e@i24g2000prf.googlegroups.com>
On Oct 31, 6:53 am, Kevin S <Sear..._at_googlemail.com> 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
Why are you using EXECUTE IMMEDIATE to run a PL/SQL packaged procedure from within PL/SQL when this works:
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS( ownname => 'LOAD',
estimate_percent => 100, cascade => TRUE,options => 'GATHER AUTO');
END;
/
Can you enlighten us as to why you think this exercise is absolutely necessary?
David Fitzjarrell Received on Fri Oct 31 2008 - 12:52:28 CDT