oracle 在 OCI 中使用 PL/SQL 变量导致“ORA-01008: not all variables bound”错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20694572/
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
Using PL/SQL variables in OCI results in "ORA-01008: not all variables bound" error
提问by Raven
The following (highly contrived and simplified) example runs fine in SQLDeveloper, but results in an ORA-01008 error when run through OCI.
以下（高度人为和简化的）示例在 SQLDeveloper 中运行良好，但在通过 OCI 运行时会导致 ORA-01008 错误。
declare
CURRENT_LINE_ID NUMBER := 120;
TARGETVAR NUMBER;
begin
SELECT 1 INTO TARGETVAR FROM DUAL WHERE 120 = :CURRENT_LINE_ID;
end;
Is there any way to restructure this so that the bind variable is satisfied in OCI?
有什么方法可以重构它，以便在 OCI 中满足绑定变量？
I experimented with substitution variablesa little (again works in SQL Developer), but DEFINE appear to be completely invalid in OCI.
我尝试了一些替换变量（再次在 SQL Developer 中工作），但 DEFINE 在 OCI 中似乎完全无效。
DEFINE MYSUBST = 120;
DECLARE
TARGETVAR NUMBER;
BEGIN
SELECT 1 INTO TARGETVAR FROM DUAL WHERE 120 = &MYSUBST;
END;
回答by Maheswaran Ravisankar
When you use :CURRENT_LINE_ID NUMBER, OCIlooks for that bind variable in your host program only. Here C++. So you should have had this variable declare in your c++ program in a exec declare sectionor wherever it should be. When you run anything in SQL developer, when encountered a :variable, it blindly prompts the user to enter the value for it, so dont mix it up with the way it do and the oci librarieswork.
使用 时:CURRENT_LINE_ID NUMBER，OCI仅在宿主程序中查找该绑定变量。在这里C++。所以你应该在你的 c++ 程序中声明这个变量，exec declare section或者它应该在的任何地方。当你在 中运行任何东西时SQL developer，当遇到 a 时:variable，它会盲目地提示用户为其输入值，所以不要将它与它的oci libraries工作方式和工作方式混淆。
In your case finally, when a PL/SQLis used and variable is declared there, you can always refer it without colon. If you want to bind it from the hostprogram, you have declare it as host variable. PRO*Csupports that. not sure about c++. pro*c is nothing but a embedded sqlin Cprovided by oracle.
最后，在您的情况下，当使用 aPL/SQL并在那里声明变量时，您始终可以在没有colon. 如果你想从主机程序绑定它，你已经将它声明为host variable. PRO*C支持。不确定 C++。PRO * C也不过是embedded sql在C提供oracle。
回答by Raven
Maheswaran's answer led me to search for host variable, which led me to the answer: I was a single colon away from success.
Maheswaran 的回答让我去寻找host variable，这让我找到了答案：我离成功只有一个冒号。
DECLARE
CURRENT_LINE_ID NUMBER := 120;
targetVar NUMBER;
begin
SELECT 1 INTO targetVar FROM DUAL WHERE 120 = CURRENT_LINE_ID;
end;
Although now it turns out that I'm going to have to wrap everything in a stored procedure to actually get the rows back.
尽管现在事实证明我将不得不将所有内容都包装在存储过程中才能真正取回行。
