oracle-databasestored-proceduresparameter-passingexecute-immediate

wrong number or types of arguments in call to Oracle stored procedure


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;
/

Solution

  • 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.