SELECT C.OPRID,
  C.OPRDEFNDESC         ,
  A.ROLENAME            ,
  A.DESCR
   FROM PSROLEDEFN A,
  PSROLEUSER B      ,
  PSOPRDEFN C
  WHERE B.ROLENAME = A.ROLENAME
AND C.OPRID        = B.ROLEUSER
AND (C.OPRID      =:1
OR C.OPRDEFNDESC LIKE upper(:2))
GROUP BY C.OPRID,
  C.OPRDEFNDESC ,
  A.ROLENAME    ,
  A.DESCR;
-- Make sure when passing the :2 value to pass % with it for the 'Like' search to work.  example :2 = %John%