Best way to use PL/SQL Pacakge Cursors from Pro*C
- by Greg Reynolds
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?
