(a) What Oracle version? (b) What is the error you are getting?(c) It could be that you have some required privileges granted through a role which does not work from within a stored proce.
(d) Why the use of dynamic sql instead of simply begindbms_stats.gather_schema_stats(ownname=>user, cascade=>true, method_opt=> 'FOR ALL COLUMNS SIZE AUTO', options=> 'GATHER');
end;(e) Are you aware that with method_opt=> 'FOR ALL COLUMNS SIZE AUTO' you are generating a ton of, largely unnecessary and potentially dangerous (for performance), histograms?
At 11:14 AM 11/16/2006, Harvinder Singh wrote:
Hi,We are getting invalid sql error when running this procedure but when I capture of v_sql during debgging and run that statement it works fine.What can be the possible issue?CREATE OR REPLACE PROCEDURE analyze_all_tables AS v_user_name varchar2(30); v_sql varchar2(4000); begin SELECT sys_context('USERENV', 'SESSION_USER') into v_user_name FROM dual; v_sql := 'begin dbms_stats.gather_schema_stats( ownname=> ''' || v_user_name || ''', cascade=> TRUE, method_opt=> ''FOR ALL COLUMNS SIZE AUTO'', options=> ''GATHER''); end;'; execute immediate v_sql; end; / Thanks --Harvinder
Regards Wolfgang Breitling Centrex Consulting Corporationwww.centrexcc.com
______________________________________________________________________ This email has been scanned by the MessageLabs Email Security System.For more information please visit http://www.messagelabs.com/email ______________________________________________________________________