Oracle 查询 - 缺少定义
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30814408/
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
Oracle Query - Missing Defines
提问by this-Mathieu
I created a very simple table:
我创建了一个非常简单的表：
CREATE TABLE TMP ("ID" VARCHAR2(20 BYTE));
Then tried to do this:
然后尝试这样做：
DECLARE
whatever varchar2(20) := :bananas;
BEGIN
MERGE INTO tmp t USING
(SELECT whatever AS this_id FROM DUAL) d
ON (t.id = d.this_id)
WHEN NOT MATCHED THEN
INSERT (id) VALUES (d.this_id);
END;
And then enter binds
然后输入绑定
And get this error:
并得到这个错误：
Error starting at line : 1 in command -
DECLARE
whatever varchar2(20) := :bananas;
BEGIN
MERGE INTO tmp2 t USING
(SELECT whatever AS this_id FROM DUAL) d
ON (t.id = d.this_id)
WHEN NOT MATCHED THEN
INSERT (id) VALUES (d.this_id);
END;
Error report -
Missing defines
I've had no luck figuring out what it wants. If I replace ':bananas' with a value like 'a' it works, but not when I use a variable and bind the value. Anyone know whats wrong with my query? Thanks.
我没有运气弄清楚它想要什么。如果我将 ':bananas' 替换为类似 'a' 的值，它会起作用，但当我使用变量并绑定该值时则不会。有人知道我的查询有什么问题吗？谢谢。
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE 11.2.0.4.0 Production"
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
edit: I've just noticed that the error is not preventing the data from being merged correctly... The error is still concerning though
编辑：我刚刚注意到该错误并没有阻止数据正确合并......尽管该错误仍然令人担忧
回答by Don Pedro
I would probable skip the DECLARE section all together and use everywhere :bananas instead of whatever. After BEGIN put :bananas := :bananas; and you'll get no error. Good luck.
我可能会一起跳过 DECLARE 部分并在任何地方使用 :bananas 而不是任何东西。BEGIN 后放 :bananas := :bananas; 你不会得到任何错误。祝你好运。
回答by WW.
Try adding a slash after the statement on a line of its own. Then highlight the whole block and press F5.
尝试在单独一行的语句后添加斜杠。然后突出显示整个块并按 F5。
回答by Dzmitry Lahoda
I had the same error message. My DB column was 32 chars(VARCHAR2(32 CHAR)) and declared variable for filter was 64 chars(szId varchar2(32) := :Id;). I copied and pasted some value as input variable. For some reason I got white space in the end. So I got 33 chars and mentioned error. I made my declaration of 32 chars and started to get more meaningful error.
我有同样的错误信息。我的数据库列是 32 个字符（VARCHAR2(32 CHAR)），过滤器的声明变量是 64 个字符（szId varchar2(32) := :Id;）。我复制并粘贴了一些值作为输入变量。出于某种原因，我最后得到了空白。所以我得到了 33 个字符并提到了错误。我声明了 32 个字符，并开始出现更有意义的错误。
I your case your DB column is VARCHAR2(20 BYTE)and declare is whatever varchar2(20) := :bananas;. I may appear that byteis not charin total size.
我你的情况你的数据库列是VARCHAR2(20 BYTE)并且声明是whatever varchar2(20) := :bananas;。我可能看起来byte不是char总尺寸。
回答by v.sheldeshov
I've just got similar case.
我刚刚有类似的情况。
After a short research the bug was found. Where was a code
经过短暂的研究，发现了这个错误。哪里有代码
DECLARE
VAR001 NUMBER := NULL;
...
SELECT *
...
AND FIELD001 = VAR001
I have forgoten to set VAR001
我忘记设置 VAR001
回答by Tony BenBrahim
In my case, I had bind OUT parameters that I tried to access before calling CallableStatement::execute().
就我而言，我绑定了在调用 CallableStatement::execute() 之前尝试访问的 OUT 参数。
