Re: Optional (ignored) parameters in stored procedures (PL/SQL)? How to?

   OPEN resultCursor FOR

     SELECT UMET_ID
       FROM T_UM_BASE
       WHERE UMBS_MEMB = memberId
          AND  UMBS_OBJ_STT = nvl(state,UMBS_OBJ_STT)

• mcs

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3ffec9a2$0$9391$ed9e5944_at_reading.news.pipex.net...
| How about overloading your function
|
| eg
|
| Function doit(memberid in T_UM_BASE.UMBS_MEMB%TYPE, state in
| T_UM_BASE.UMBS_MEMB%TYPE);
| Function doit(memberid in T_UM_BASE.UMBS_MEMB%TYPE);
|
| with appropriate selects in each one.
|
| --
| Niall Litchfield
| Oracle DBA
| Audit Commission UK
| "Kurta" <submit_at_galleus.com> wrote in message
| news:efcb1994.0401090716.5da3c1de_at_posting.google.com...
| > Hi,
| >
| > State is a parameter of the function. When it's NULL, we don't need to
| query for it.
| >
| > Is there a way to replace this peace of code:
| > .
| > .
| > IF (state IS NOT NULL) THEN
| > OPEN resultCursor FOR
| > SELECT UMET_ID
| > FROM T_UM_BASE
| > WHERE UMBS_MEMB = memberId
| > AND UMBS_OBJ_STT = state;
| > ELSE
| > OPEN resultCursor FOR
| > SELECT UMET_ID
| > FROM T_UM_BASE
| > WHERE UMBS_MEMB = memberId;
| > END IF;
| > .
| > .
| > ...with a more elegant solution __without using dynamic SQL__ in a
stored
| function?
| >
| > This may work in MSSQL but doesn't work in Oracle:
| > OPEN resultCursor FOR
| > SELECT UMET_ID
| > FROM T_UM_BASE
| > WHERE UMBS_MEMB = memberId
| > AND UMBS_OBJ_STT = COALESCE(state, UMBS_OBJ_STT);
| >
| > Any help?
| >
| > Thanks
|
|
Received on Fri Jan 09 2004 - 10:53:26 CST