Monday, January 12, 2009

Oracle: Calling PL/SQL procedures that return a cursor

How does one go about calling a PL/SQL procedure that returns a cursor as an OUT parameter from a SQL script?
For example how do you call the following procedure?

PROCEDURE GETCURSORS (
DEPARTMENTID IN NUMBER,
EMPLOYEESCUR OUT SYS_REFCURSOR)

Well, you use a bind variable. Here is how to do it:
variable retCur refcursor; --declare the bind variable
exec GetCursor(119,:retCur); --call the procedure and have the bind variable filled out
print :retCur; -- print the results to output - this is handy for debugging

This is truly handy for debugging. And here is how one can use bind variables to increase application performance.

Bind variables - The key to application performance.