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

CodePudding user response：

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.

CodePudding user response：

Another version of the same solution with a bit more of control on how to truncate the table and a debug to see the command executed.

Let me show you the demo

SQL> create table test3.t1 ( c1 number, c2 number ) ;

Table created.

SQL> insert into test3.t1 select level, level from dual connect by level <= 10000 ;

10000 rows created.

Then I create my own truncate procedure

SQL> create or replace procedure test3.do_truncate ( ptablename in NVARCHAR2, poption in varchar2, pdebug in boolean default true )
  2  is
  3  vcount pls_integer;
  4  vsql varchar2(400);
  5  begin
  6   vsql := 'truncate table ' || ptablename || ' ' || poption || ' storage ';
  7   select count(*) into vcount from user_tables where table_name = upper(ptablename) ;
  8   if vcount = 1
  9   then
 10     if pdebug
 11     then
 12             dbms_output.put_line(vsql);
 13     end if;
 14     execute immediate vsql;
 15   else
 16     raise_application_error(-20001,'Table '||ptablename||' does not exist in schema TEST3 ');
 17   end if;
 18* end;

 Procedure created.

SQL> exec test3.do_truncate( 'T1' , 'REUSE' , true ) ;
truncate table T1 REUSE storage

PL/SQL procedure successfully completed.

SQL> exec test3.do_truncate( 'T2' , 'DROP' , true ) ;
BEGIN test3.do_truncate( 'T2' , 'DROP' , true ) ; END;

*
ERROR at line 1:
ORA-20001: Table T2 does not exist in schema TEST3
ORA-06512: at "TEST3.DO_TRUNCATE", line 16
ORA-06512: at line 1

If you want to control the option as well

create or replace procedure test3.do_truncate ( ptablename in NVARCHAR2, poption in varchar2, pdebug in boolean default true )
 is
 vcount pls_integer;
 vsql varchar2(400);
 begin
  if upper(poption) not in ('DROP','REUSE') 
  then 
     raise_application_error(-20002,'Specify DROP or REUSE for storage option in truncate');
  end if;
  vsql := 'truncate table ' || ptablename || ' ' || poption || ' storage ';
  select count(*) into vcount from user_tables where table_name = upper(ptablename) ;
  if vcount = 1
  then
    if pdebug
    then
            dbms_output.put_line(vsql);
    end if;
    execute immediate vsql;
  else
    raise_application_error(-20001,'Table '||ptablename||' does not exist in schema TEST3 ');
  end if;
 end;

Comments

• It is better to control the error inside the procedure in case you are truncating table that it does not exist.
• The GRANT itself as @MTO has already explain should not be inside the procedure. That would be necessary if you were to drop/create a table.
• You can use two options for truncate
  • reuse storage
  • drop storage

Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This is the default.

Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations. This clause leaves storage parameters at their current settings.