Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Problem running dbms_sql inside the procedure

Re: Problem running dbms_sql inside the procedure

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Thu, 16 Nov 2006 11:37:28 -0700
Message-Id: <20061116183651.CC4C24B36BF@turing.freelists.org>

(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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 16 2006 - 12:37:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US