Oracle SQL Developer - 索引处缺少 IN 或 OUT 参数:: 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3762404/
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 SQL Developer - Missing IN or OUT parameter at index:: 1
提问by sammy
I am having trouble testing this simple stored procedure in Oracle Sql Developer. The stored procedure does a simple select and returns a cursor.
我在 Oracle Sql Developer 中测试这个简单的存储过程时遇到问题。存储过程执行一个简单的选择并返回一个游标。
create or replace
PROCEDURE GET_PROJECT_DRF_HISTORY
( projectId IN NUMBER,
resultset_out OUT sys_refcursor
) AS
BEGIN
OPEN resultset_out for
SELECT * from GLIDE_HISTORY
where GLIDE_HISTORY.PRJ_ID = projectId;
/* DBMS_OUTPUT.PUT_LINE(resultset_out);*/
END GET_PROJECT_DRF_HISTORY;
To test this procedure, I used the below script:
为了测试这个过程，我使用了以下脚本：
variable results sys_refcursor;
exec get_project_drf_history(3345, :results);
print :results;
Being new to both oracle and the Sql Developer tool, I am struggling to understand what is the mistake here. I cannot check this in Sql*Plus because I dont have the password to do so. I am using Oracle Sql Developer 1.1.2.25 and Oracle 10g.
作为 oracle 和 Sql Developer 工具的新手，我很难理解这里的错误是什么。我无法在 Sql*Plus 中检查这个，因为我没有这样做的密码。我使用的是 Oracle Sql Developer 1.1.2.25 和 Oracle 10g。
Can anybody help me out please? Thank you in advance.
有人可以帮我吗？先感谢您。
回答by Rajesh Chamarthi
Sammy,
萨米，
The variable declaration should be refcursorinstead of sys_refcursor. Also when you print the results, you are printing the variable itself, so there is no need for a :(which is used to indicateit is a bind variable).
变量声明应该是refcursor而不是 sys_refcursor。此外，当您打印结果时，您正在打印变量本身，因此不需要:（用于指示它是绑定变量）。
I was able to run the following script sucessfully in SQL Developer (and of course sql plus.) For SQL Developer, run it as a script using F5.
我能够在 SQL Developer（当然还有 sql plus）中成功运行以下脚本。对于 SQL Developer，使用 F5 作为脚本运行它。
--Creating Procedure
create or replace procedure test_ref(
i_limit number,
o_results out sys_refcursor
) is
begin
open o_results for
'select object_name
from all_objects
where rownum < ' || i_limit;
end;
/
And then the script that calls this procedure. (excute as a script using F5).
然后是调用此过程的脚本。（使用 F5 作为脚本执行）。
var c1 refcursor;
exec test_ref(10,:c1);
print c1;
回答by user158017
I recommend asking your administrator to upgrade your SQL Developer version. Yours is significantly outdated and you may be running into some obscure bugs. (I did when I tried to use version 1) You should be on 2.1 by now.
我建议让您的管理员升级您的 SQL Developer 版本。你的已经过时了，你可能会遇到一些晦涩的错误。（当我尝试使用版本 1 时我这样做了）您现在应该在 2.1 上。
回答by Harrison
here is a working example, declare the refcursor then assign the value by calling you proc in an anonymous block. then you print it
这是一个工作示例，声明 refcursor 然后通过在匿名块中调用 proc 来分配值。然后你打印它
var x REFCURSOR ;
declare
/*a no cleanup procedure*/
procedure GetMeMyRefCursor(outter out nocopy sys_refcursor)
as
begin
open outter for
select level
from dual
connect by level <= 5;
end GetMeMyRefCursor;
begin
GetMeMyRefCursor(:x);
/*note you pass in the refcursor you created via the :X*/
end ;
/
print x;
/*now print it*/
/*LEVEL
----------------------
1
2
3
4
5*/
based on comment: now using your comment, you are having an issue with IN/OUT params and not with the print (didn't read the title just the question and the other response)
基于评论：现在使用您的评论，您遇到了输入/输出参数的问题，而不是打印（没有阅读标题只是问题和其他回复）
this works: (based on your code)
这有效：（基于您的代码）
create or replace
PROCEDURE GET_PROJECT_DRF_HISTORY
( projectId IN NUMBER,
resultset_out OUT sys_refcursor
) AS
BEGIN
OPEN resultset_out for
SELECT level from dual connect by level <= projectId;
/* DBMS_OUTPUT.PUT_LINE(resultset_out);*/
END GET_PROJECT_DRF_HISTORY;
/
var results REFCURSOR;
--this needs to be REFCURSOR (at least in 10g and 11i)
exec GET_PROJECT_DRF_HISTORY(5, :results);
print results;
/
You can also debug packages and procedures directly from SQL Developer (this can be a real life saver) if you want to debug in SQL Developer it is really easy:
如果你想在 SQL Developer 中调试，你也可以直接从 SQL Developer 调试包和过程（这可以是一个真正的救星）它真的很容易：
in the connections->your schema here-->procedures -> GET_PROJECT_DRF_HISTORY right click and 'Compile for debug'. Then in the procedure place a break point in it, then right click and 'debug' it (this will create an anonymous block -- see below -- where you can put in your values and such)
在连接中->您的架构在这里-->过程-> GET_PROJECT_DRF_HISTORY 右键单击并“编译以进行调试”。然后在程序中放置一个断点，然后右键单击并“调试”它（这将创建一个匿名块-见下文-您可以在其中输入值等）
DECLARE
PROJECTID NUMBER;
RESULTSET_OUT sys_refcursor;
BEGIN
PROJECTID := NULL;
GET_PROJECT_DRF_HISTORY(
PROJECTID => PROJECTID,
RESULTSET_OUT => RESULTSET_OUT
);
-- Modify the code to output the variable
-- DBMS_OUTPUT.PUT_LINE('RESULTSET_OUT = ' || RESULTSET_OUT);
END;
otherwise, the error doesn't look as it should appear if you are doing this all from Developer.
否则，如果您从 Developer 执行这一切操作，则错误看起来不会像它应该出现的那样。
But what I really think is happening is your VAR is incorrect and thus it doesn't exists!
但我真正认为正在发生的是您的 VAR 不正确，因此它不存在！
variable results sys_refcursor;
Usage: VAR[IABLE] [ <variable> [ NUMBER | CHAR | CHAR (n [CHAR|BYTE]) |
VARCHAR2 (n [CHAR|BYTE]) | NCHAR | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | REFCURSOR |
BINARY_FLOAT | BINARY_DOUBLE ] ]
so, going from my initial example "var x REFCURSOR ;" should work
所以，从我最初的例子“var x REFCURSOR ;”开始 应该管用
