Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

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

You Asked

What is the best way to handle a query with multiple variables, and some of the variables can be null, like:

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;


Thanks for your reply.

and Tom said...

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Link not working

A reader, August 02, 2018 - 7:58 am UTC

Link isn't available any more
Chris Saxon
August 02, 2018 - 3:21 pm UTC

Thanks, I've fixed the link.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library