Difference between "IN" and "IN OUT" CURSOR parameter in Oracle

Question

From Oracle: "When you declare a cursor variable as the formal parameter of a subprogram that fetches from the cursor variable, you must specify the IN or IN OUT mode. If the subprogram also opens the cursor variable, you must specify the IN OUT mode."

But, I can code that (only OUT parameter):

create or replace procedure mycur_out(mc OUT mycurpkg.mytypecur)  as 
begin
    open mc for select * from mytable; 
end mycur_out;

and works equal to (IN OUT parameter)

create or replace procedure mycur_inout(mc IN OUT mycurpkg.mytypecur) 
as
begin
    open mc for select * from table10;
end mycur_inout;

Also, It's work fine with dynamic cursor too:

create or replace procedure mycur_out_ref(mc out mycurpkg.mytyperefcur) 
as
begin
    open mc for 'select * from table10';
end mycur_out_ref;

I've tested the 3 cases directly from oracle and from VB6 with ADO, and no problems. So, in that cases, is there any difference between IN using just "OUT" and "IN OUT" cursors parameters?

UPDATE The reason I'm asking:

• We read data using routines similar to the examples (just open the cursors). The cursor parameters always are "IN OUT" (Don't ask me why, I'm trying to figure out)
• The routines are invoked with ADO/VB6
• Now, we are trying to use some of the routines from JDBC, but the adapter apparently just accepts OUT parameters in this cases.
• Finally, the main reason, I want to change the cursor parameters on DB routines to only OUT, but first I want to know the collaterals effects of that change.

Thanks!

Answer 1

If I understand the question right, the difference is that with the IN OUT version you can pass in a cursor from outside the procedure, and then change that variable (similar to the difference between OUT and IN OUT for a simple numeric variable).

The OUT parameter cursor starts out as a NULL value / closed cursor.

The IN OUT parameter version starts with whatever state is passed in from outside.

You may want to retry your procedure calls repeatedly passing in the same cursor variable - the OUT version should replace the existing value, the IN OUT version should give an exception on the second time round that you are trying to open an open cursor.

Another thing the IN OUT approach allows, that the OUT approach does not, is to take action based on the passed in cursor, and change the returned cursor.

PROCEDURE lp_test2 (mc IN OUT mycurpkg.mytypecur)
IS
  lr table10%ROWTYPE;
BEGIN 
   IF mc%ISOPEN THEN 
      FETCH mc INTO lr;
      IF mc%NOTFOUND THEN
         CLOSE mc;
         /* Switch cursor to alternative table */
         open mc for select * from schema2.table10;
      END IF;
   END IF;
END lp_test2;

I am just struggling to think of a real situation where you might want to (take in a cursor variable, cast it back into a SQL statement, append some extra dynamic SQL, and return the whole lot back as the same cursor??).

Answer 2

In the text you quote from the manual, note that it is specifically talking about "a subprogram that fetches from the cursor variable". None of your examples do this, so the quote is not relevant to them.

However, it nonetheless appears that there's nothing wrong with using OUT only in such a situation, if the subprogram both opens and fetches from the cursor variable:

SQL> variable c refcursor

SQL> set serveroutput on
SQL> create or replace procedure no_good (c  OUT sys_refcursor)
  2     as
  3        my_dummy  dual.dummy%type;
  4     begin
  5       open c for select dummy from dual union all select dummy from dual;
  6       fetch  c into my_dummy;
  7       dbms_output.put_line( my_dummy );
  8     end;
  9  /

Procedure created.

SQL> exec no_good( :c )
X

PL/SQL procedure successfully completed.

SQL> print c

D
-
X

I think the the text is actually trying to make two points that are somewhat independent of each other. Firstly, if you want to pass any already-opened cursor variable into a subprogram, which will fetch from it, the parameter must be declare IN or IN OUT. Secondly, if you want to pass a cursor variable into a subprogram, which will then open it, the parameter must be declared OUT or IN OUT. This is true regardless of whether you actually care about passing the value of the cursor variable back to the caller:

SQL> create or replace procedure no_good (c  IN sys_refcursor)
  2     as
  3        my_dummy  dual.dummy%type;
  4     begin
  5       open c for select dummy from dual;
  6       fetch  c into my_dummy;
  7       dbms_output.put_line( my_dummy );
  8       close c;
  9     end;
 10  /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE NO_GOOD:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/6      PL/SQL: SQL Statement ignored
5/11     PLS-00361: IN cursor 'C' cannot be OPEN'ed

This error can be fixed by changing the parameter mode, but actually it would seem to make more sense to simply make the cursor variable a local variable rather than a parameter.