P/L SQL: Calling a package from a package

From: DBA_Dawg <naushad.bhamani_at_bcbsga.com>
Date: 31 Mar 2004 06:46:17 -0800
Message-ID: <881b72a9.0403310646.1f9cd72a@posting.google.com>

Hello Gurus,

Our requirement is to grant a service account to run DBMS_STATS after their monthly load. I am writing a package as schema owner to grant the service account this privilege. The package accepts paramaters like schema_owner, table_name, estimate_pecentage. I take those variable and create a dynamic sql to be executed. When I execute the package I get

DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> 'MY_SCHEMA', TABNAME=> 'MY_TABLE', ESTIMATE_PERCENT=> 5 , degree=> 2, CASCADE=> TRUE) ORA-00900: invalid SQL statement

My Package is looks like this:

create or replace package NAB_UTLS is

      procedure TABLE_STATS (P_SCHEMA_OWNER in varchar2, P_OBJ_NAME in varchar2, P_EST_PCT in number);
end NAB_UTLS;
/

show errors;

create or replace package body NAB_UTLS as  procedure TABLE_STATS
  (P_SCHEMA_OWNER in varchar2, P_OBJ_NAME in varchar2, P_EST_PCT in number) AS
  begin
  declare

  v_sql_str varchar2(200) := ' ';
  v_ErrorCode NUMBER;
  v_Errortext varchar2(512);
  begin
        v_sql_str := 'DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=> ''' ||

P_SCHEMA_OWNER || ''', TABNAME=> ''' || P_OBJ_NAME || ''', ESTIMATE_PERCE
NT=> ' || P_EST_PCT || ' , degree=> 2, CASCADE=> TRUE)';

     DBMS_OUTPUT.put_line (v_sql_str);
     EXECUTE IMMEDIATE ( v_sql_str );
 
     EXCEPTION
     WHEN OTHERS THEN
      begin
       v_ErrorCode := SQLCODE;
       v_Errortext := SQLERRM;
       DBMS_OUTPUT.PUT_LINE( v_Errortext );
      end;

  END;
 end TABLE_STATS;
end NAB_UTLS;
/

show errors;

Please help
thanks
NB Received on Wed Mar 31 2004 - 08:46:17 CST