Re: Problem running dbms_sql inside the procedure

(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
   begin
dbms_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 Corporation
www.centrexcc.com 
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email ______________________________________________________________________

Other related posts: