this is how my stored procedure is set up. Since it returns a cursor, I cannot close it in the procedure.

PACKAGE Return_Agent_ID AS

TYPE tableResult IS RECORD(
Agent_ID NUMBER);

TYPE myCursorType IS REF CURSOR RETURN tableResult;

PROCEDURE ReturnResult(strLastName IN VARCHAR2,
strFirstName IN VARCHAR2,
strSite IN VARCHAR2,
p_cursor OUT Return_Agent_ID.myCursorType);

END Return_Agent_ID;


PACKAGE BODY Return_Agent_ID AS

PROCEDURE ReturnResult (strLastName IN VARCHAR2,
strFirstName IN VARCHAR2,
strSite IN VARCHAR2,
p_cursor OUT Return_Agent_ID.myCursorType)
IS

BEGIN
OPEN p_cursor FOR

Select ag.agent_id
from agent ag
where site = strSite and
upper(trim(ag.lname)) = strLastName and
upper(trim(ag.fname)) = strFirstName;

END ReturnResult;

END Return_Agent_ID;