呼叫Oracle存盤程序時的引數數量或型別錯誤

我在 Oracle 引數化存盤程序下面創建了我試圖將Truncate表特權授予另一個用戶但得到錯誤為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;
/

uj5u.com熱心網友回復：

您在TRUNCATE陳述句中缺少一個空格字符，并且程序名稱應該在GRANT陳述句中的字串文字中（而不是嘗試動態附加它）：

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

我也不確定GRANT在程式中包含 的價值是什么。您只需要運行GRANT一次，并且可以在程式之外使用特權用戶執行此操作；顯然沒有必要將其包含在程式中。

uj5u.com熱心網友回復：

相同解決方案的另一個版本，對如何截斷表和除錯以查看執行的命令有更多的控制。

讓我給你演示一下

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.

然后我創建自己的截斷程序

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

如果您還想控制該選項

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;

注釋

• 最好控制程式內部的錯誤，以防您截斷它不存在的表。
• GRANT@MTO 已經解釋過的本身不應該在程式內部。如果您要洗掉/創建表，那將是必要的。
• 您可以使用兩個選項進行截斷
  • 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.

標籤：甲骨文 存储过程 参数传递 立即执行

上一篇：為每個表行創建n分鐘間隔

下一篇：如何使用currval獲取所有序列的值？