Problem running dbms_sql inside the procedure

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
 

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 16 2006 - 12:14:23 CST