Thanks for the question, Eric.
Asked: June 04, 2012 - 11:58 am UTC
Last updated: August 02, 2018 - 3:21 pm UTC
Version: 10.1.2.0.2
Viewed 1000+ times
FUNCTION GET_RECIPE(P_RECIPE_LIST IN VARCHAR2, P_OWNER_LIST IN VARCHAR2, P_CREATED_BY IN NUMBER, P_RECIPE_CREATED_START IN DATE, P_RECIPE_CREATED_END IN DATE,) RETURN SYS_REFCURSOR IS O_REF SYS_REFCUSROR; BEGIN OPEN O_REF FOR SELECT R.RECIPE_ID FROM recipe r WHERE (P_RECIPE_LIST IS NULL OR R.RECIPE_ID IN (SELECT COLUMN_VALUE COLLECT_REQUEST_ID FROM TABLE(LISTTOCOLLECTION(P_RECIPE_LIST)))) AND (P_OWNER_ID IS NULL OR p.OWNER_ID IN (SELECT COLUMN_VALUE MRN FROM TABLE(LISTTOCOLLECTION(P_OWNER_LIST)))) AND R.CREATED_BY = NVL(P_CREATED_BY, R.CREATED_BY) AND R.CREATED_DATE BETWEEN NVL(P_RECIPE_CREATED_START, R.CREATED_DATE) AND NVL(P_RECIPE_CREATED_END, R.CREATED_DATE); RETURN O_REF; END;
A reader, August 02, 2018 - 7:58 am UTC
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library