Re: using EXECUTE IMMEDIATE to run DBMS_STATS.GATHER_SCHEMA_STATS

On Oct 31, 11: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

If you do want to call a PL/SQL procedure dynamically (although I can't see why you would want to here) it would need to be 'CALL' and not the SQL*Plus command 'EXEC', or else enclose it within a 'BEGIN...END;' block (including the PL/SQL semicolon not required by the SQL 'CALL'). Received on Sun Nov 02 2008 - 09:37:23 CST