using EXECUTE IMMEDIATE to run DBMS_STATS.GATHER_SCHEMA_STATS

[Kevin S]

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

[gym dot scuba dot kennedy at gmail]

"Kevin S" <Sea...@googlemail.com> wrote in message
news:c285fc93-2f58-4acb...@w39g2000prb.googlegroups.com...

You don't need the ; inside the quotes. Also the load etc strings need to
be single quoted not double quoted.'
Jim

[Michel Cadot]

"Kevin S" <Sea...@googlemail.com> a écrit dans le message de news:
c285fc93-2f58-4acb...@w39g2000prb.googlegroups.com...

Also EXEC is a SQL*Plus command not a PL/SQL one.

Regards
Michel

[ddf]

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

[William Robertson]

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').

[abani.n...@gmail.com]

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

[joel garry]

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
--
@home.com is bogus.
http://www.utsandiego.com/news/2012/sep/19/prosecutors-accuse-21-people-financial-aid-fraud/