Please ignore I figured it out.
Thanks
NB
news:<881b72a9.0403310646.1f9cd72a_at_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 - 14:25:23 CST
