I have created below Oracle parameterized stored procedure where i am trying to grant Truncate
table priviledge to another user but getting error as wrong number or types of arguments in call to DO_TRUNCATE
.
create or replace procedure "DWH_02"."DO_TRUNCATE" (truncate_tablename NVARCHAR2)
IS
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE' || truncate_tablename;
EXECUTE IMMEDIATE 'grant execute on ' || DWH_02.DO_TRUNCATE ||' TO DWH_ST';
end;
/
You are missing a space character in the TRUNCATE
statement and the procedure name should be in the string literal in the GRANT
statement (rather than trying to dynamically append it):
create or replace procedure "DWH_02"."DO_TRUNCATE" (truncate_tablename NVARCHAR2)
IS
begin
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || truncate_tablename;
-- ^ Here
EXECUTE IMMEDIATE 'grant execute on DWH_02.DO_TRUNCATE TO DWH_ST';
end;
/
I am also not sure what the value of including the GRANT
in the procedure is. You only need to run the GRANT
once and could do that with a privileged user outside of the procedure; there is no apparent need to include it inside the procedure.