Best way to use PL/SQL Pacakge Cursors from Pro*C

Question

I have a cursor defined in PL/SQL, and I am wondering what the best way to use it from Pro*C is. Normally for a cursor defined in Pro*C you would do:

EXEC SQL DECLARE curs CURSOR FOR SELECT 1 FROM DUAL;
EXEC SQL OPEN curs;
EXEC SQL FETCH curs INTO :foo;
EXEC SQL CLOSE cusr;

I was hoping that the same (or similar) syntax would work for a packaged cursor. For example, I have a package MyPack, with a declaration

type MyType is record (X integer);
cursor MyCurs(x in integer) return MyType;

Now I have in my Pro*C code a rather unsatisfying piece of embedded PL/SQL that opens the cursor, does the fetching etc., as I couldn't get the first style of syntax to work. Using the example

EXEC SQL EXECUTE
  DECLARE
    XTable is table of MyPack.MyType;
  BEGIN
    OPEN MyPack.MyCurs(:param);
    FETCH MyPack.MyCurs INTO XTable;
    CLOSE MyPack.MyCurs;
  END;
END-EXEC;

Does anyone know if there is a more "Pure" Pro*C approach?

Answer 1

Yes. Do a google search on the term REF CURSOR and you should see examples of what you want to do.

Answer 2

It actually would not be much different than your first example, just make sure your cursor definition is in the package spec and not the package body. Don't "declare" it and just go with something like:

Database object:

create or replace package mypkg as 
  cursor mycur is 
    SELECT 1 FROM DUAL; 
end;

In pro*c:

EXEC SQL OPEN schema.mypkg.mycur; 
EXEC SQL FETCH schema.mypkg.mycur INTO :foo; 
EXEC SQL CLOSE schema.mypkg.mycur; 

Of course the package would need to be accessible to the oracle user you are connecting with, etc. If the package is owned by the user connecting, or there is a synonym in place, the "schema." is not required in the pro*c calls.