Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> 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 grantedthrough 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
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 16 2006 - 12:37:28 CST
![]() |
![]() |