Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> P/L SQL: Calling a package from a package
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
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;
Please help
thanks
NB
Received on Wed Mar 31 2004 - 08:46:17 CST