Oracle SQL Developer - Missing IN or OUT parameter at index:: 1

Question

Hi,

I am having trouble testing this simple stored procedure in Oracle Sql Developer. The stored procedure does a simple select and returns a cursor.

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.

Can anybody help me out please? Thank you in advance.

Answer 1

here is a working example, declare the refcursor then assign the value by calling you proc in an anonymous block. then you print it

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:

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)

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;

(http://www.oracle.com/technetwork/developer-tools/sql-developer/sqldeveloperwhitepaper-v151-130908.pdf page 11)

otherwise, the error doesn't look as it should appear if you are doing this all from Developer.

But what I really think is happening is your VAR is incorrect and thus it doesn't exists!

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

Answer 2

Sammy,

The variable declaration should be refcursor instead 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).

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.

--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).

var c1 refcursor;

exec test_ref(10,:c1);

print c1;

Answer 3

Thank you Rajesh and Tanging. I appreciate your help. I did try your samples it still had the same error. I dont know what the issue is. I m guessing it has got something to do with the tool. I connected the stored proc to my code in .NET and tested there. It is returning the results as expected. So I m calling off my debugging in SQL Developer. Once again, thank you very much guys.

Answer 4

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.