无效的 SQL 类型：sqlKind = UNINITIALIZED - PLSQL 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29661989/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Invalid SQL type: sqlKind = UNINITIALIZED - PLSQL Error
提问by user311509
I simply want to prevent users from running UPDATE statement without WHERE clause. I provided my PLSQL below
我只是想阻止用户在没有 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;
That's how I execute it
这就是我执行它的方式
EXEC secure_update
('
UPDATE dummy_table
SET col1 = ''whatever''
WHERE pk = ''1234''
')
I keep getting this message:
我不断收到此消息：
Invalid SQL type: sqlKind = UNINITIALIZED
无效的 SQL 类型：sqlKind = UNINITIALIZED
Can you please help me find out how to overcome this error?
你能帮我找出如何克服这个错误吗？
采纳答案by anudeepks
This works, please see the changes, dont use the into clause in execute immediate
这是有效的，请查看更改，不要在立即执行中使用 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;
if you want to use varchar in update then please see this
如果你想在更新中使用 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
another example
另一个例子
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.
回答by Alex Poole
SQL Developer (and SQL*Plus, but possibly not other clients!) needs the execcommand to be on a single line. You didn't show the earlier errors you would have got from that invocation:
SQL Developer（和 SQL*Plus，但可能不是其他客户端！）需要将exec命令放在一行中。您没有显示从该调用中获得的早期错误：
EXEC secure_update
Error report -
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SECURE_UPDATE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Error starting at line : 25 in command -
('
UPDATE dummy_table
SET col1 = ''whatever''
WHERE pk = ''1234''
')
Error at Command Line : 25 Column : 2
Error report -
SQL Error: Invalid SQL type: sqlKind = UNINITIALIZED
The procedure is being called with no arguments, because there are none on the same line as the actual exec, so you get a PLS-00306 from that line. Then the remainder is interpreted as a separate command, which gets the 'invalid SQL type' error you reported. (In SQL Developer, that is; in SQL*Plus you'd get the same PLS-00306 for the first part, but ORA-00928: missing SELECT keywordfor the rest).
调用该过程时不带参数，因为没有参数与实际 位于同一行exec，因此您会从该行获得 PLS-00306。然后其余部分被解释为一个单独的命令，它得到你报告的“无效的 SQL 类型”错误。（在 SQL Developer 中，也就是说；在 SQL*Plus 中，第一部分将获得相同的 PLS-00306，但ORA-00928: missing SELECT keyword对于其余部分）。
You can either move the whole statement into one line:
您可以将整个语句移到一行中：
EXEC secure_update ('UPDATE dummy_table SET col1 = ''whatever'' WHERE pk = ''1234''');
Or use an explicit anonymous block rather than the execshorthand:
或者使用显式匿名块而不是exec速记：
BEGIN
secure_update
('UPDATE dummy_table
SET col1 = ''whatever''
WHERE pk = ''1234''
');
dbms_output.put_line('after');
END;
/
Note also that I've had to move the UPDATEup a line anyway, as your check doesn't allow for any whitespace (including a line break) at the start of the command. The into msgin your procedure isn't doing anything but doesn't seem to be causing a problem; if you want to see how many rows were updated use SQL%ROWCOUNT after the execute immediateinstead.
另请注意，UPDATE无论如何我都必须向上移动一行，因为您的检查不允许在命令开头有任何空格（包括换行符）。该into msg在你的程序中没有做任何事情，但似乎并没有引起问题; 如果您想查看更新了多少行，请改用 SQL%ROWCOUNT execute immediate。
回答by Jon Heller
An UPDATEstatement should not be executed intoanything, it should just be executed. This code demonstrates the error, although the error message is slightly different than the one you posted.
一个UPDATE语句不应该被执行into任何东西，它应该被执行。此代码演示了错误，尽管错误消息与您发布的消息略有不同。
create table dummy_table(col1 varchar2(100), pk number);
declare
msg varchar2(30000);
begin
execute immediate q'[
UPDATE dummy_table
SET col1 = 'whatever'
WHERE pk = '1234'
]' into msg;
end;
/
ORA-01007: variable not in select list
ORA-06512: at line 4
Remove the into msgand it will work. Also, the alternative quoting mechanism is used to avoid escaping quotation marks.
删除into msg它，它将起作用。此外，替代引用机制用于避免转义引号。
