sql - 无效的 SQL 类型 : sqlKind = UNINITIALIZED - PLSQL Error

Question

我只是想阻止用户运行没有 WHERE 子句的 UPDATE 语句。我在下面提供了我的 PLSQL

create or replace PROCEDURE secure_update(update_query IN varchar2)
IS
  msg varchar2(30000);
  flag char(1);
  qry varchar2(30000);
BEGIN

  IF upper(update_query) LIKE 'UPDATE%SET%WHERE%=%' THEN
    flag := '1';
  ELSE
    flag := '0';
  END IF;

  IF (flag = '1') THEN
    --qry := update_query;
    execute immediate update_query into msg;
  END IF;

  dbms_output.put_line(msg);

END;

这就是我执行它的方式

EXEC secure_update
('
UPDATE dummy_table
SET col1 = ''whatever''
WHERE pk = ''1234''
')

我不断收到此消息:

Invalid SQL type: sqlKind = UNINITIALIZED

您能帮我找出如何克服这个错误吗？

Answer 1

这有效，请查看更改，不要在立即执行中使用 into 子句

create or replace PROCEDURE secure_update(update_query IN varchar2)
IS
  msg varchar2(30000);
  flag char(1);
  qry varchar2(30000);
BEGIN

  IF upper(update_query) LIKE 'UPDATE%SET%WHERE%=%' THEN
    flag := '1';
    dbms_output.put_line('updated succesfully');
  ELSE
    flag := '0';
    dbms_output.put_line('no where clause in update');
  END IF;

  IF (flag = '1') THEN
    --qry := update_query;
    execute immediate update_query ;
  END IF;

  END;

如果您想在更新中使用 varchar，请参阅此

SCOTT@research 16-APR-15> select * from test2;

A     B
----- -----
a     b

code to execute procedure :

declare
lsql varchar2(100):= 'update test2 set a=''z'' where b=''b'' ';
begin
secure_update(lsql);
end;

output:     updated succesfully

SCOTT@research 16-APR-15> select * from test2;

A     B
----- -----
z     b

declare
lsql varchar2(100):= 'update test2 set a=''z''';
begin
secure_update(lsql);
end;

output

no where clause in update    

另一个例子

SCOTT@research 16-APR-15> select * from test1;

      VAL1       VAL2       VAL3
---------- ---------- ----------
         2          2          4
         3          2          4
       123          2          3
        42          3

SCOTT@research 16-APR-15> exec secure_update('update test1 set val1=555 where val1=2');
updated succesfully

PL/SQL procedure successfully completed.

SCOTT@research 16-APR-15> select * from test1;

      VAL1       VAL2       VAL3
---------- ---------- ----------
       555          2          4
         3          2          4
       123          2          3
        42          3


SCOTT@research 16-APR-15> exec secure_update('update test1 set val1=555');
no where clause in update

PL/SQL procedure successfully completed.