Votes

Provide a way to tell if the value of a parameter was 1) passed in or was 2) assigned a default value. (details)
 
Dear Bryn,

I love PL/SQL and it would sure be nice to be able to tell from within a program if the caller of the program actually passed a value or if the value came from the default (that is, the caller did not provide a value for that argument).

I have a procedure whose job it is to update only those fields of a table that the calling module passes me a value for. The difficulty is that if you make the parameters optional by assigning each a default value (such as null), I still have no way of knowing if the user passed me a null or if the parameter was defaulted to null. The work-around to this issue is to default each of the parameters to some bogus value (such as -999999) and then check to see if the parameter is that value. If it is, then the parameter value was not passed in. This is not very intuitive. Besides, what if someday I want to use -999999 as an actual value for that parameter?


One way of implementing this is to provide a new built-in function called npi (for Not Passed In). The function would return TRUE if the value was not passed in and FALSE if the value was passed in.

Note: Bryn Llewellyn has submitted ER 6637787 to implement this idea.


CREATE OR REPLACE PROCEDURE CHANGE (
   i_empno emp.empno%TYPE
 , i_ename emp.ename%TYPE DEFAULT NULL
 , i_job emp.job%TYPE DEFAULT NULL
 , i_hiredate emp.hiredate%TYPE DEFAULT NULL
 , i_sal emp.sal%TYPE DEFAULT NULL
 , i_comm emp.comm%TYPE DEFAULT NULL
 , i_deptno emp.deptno%TYPE DEFAULT NULL
)
IS
BEGIN
   UPDATE emp
      SET ename = i_ename
        , job = NVL (i_job, job)
        , hiredate = NVL (i_hiredate, hiredate)
        , sal = NVL (i_sal, sal)
        , comm = NVL (i_comm, comm)
        , deptno = NVL (i_deptno, deptno)
    WHERE emp.empno = i_empno;
END;

/* Value before call */
SELECT comm
  FROM emp
 WHERE empno = 7499
/
/* 300 */

/* Calling Code will NOT set comm to null*/

BEGIN
   -- Call the procedure
   pk_emp.CHANGE (i_empno => 7499, i_comm => NULL);
END;
/

/* Value after call */
SELECT comm
  FROM emp
 WHERE empno = 7499
/
/* 300 */


CREATE OR REPLACE PROCEDURE CHANGE (
   i_empno emp.empno%TYPE
 , i_ename emp.ename%TYPE DEFAULT NULL
 , i_job emp.job%TYPE DEFAULT NULL
 , i_hiredate emp.hiredate%TYPE DEFAULT NULL
 , i_sal emp.sal%TYPE DEFAULT NULL
 , i_comm emp.comm%TYPE DEFAULT NULL
 , i_deptno emp.deptno%TYPE DEFAULT NULL
)
IS
BEGIN
   UPDATE emp
      SET ename = i_ename
        , job = npi (i_job, job)
        , hiredate = npi (i_hiredate, hiredate)
        , sal = npi (i_sal, sal)
        , comm = npi (i_comm, comm)
        , deptno = npi (i_deptno, deptno)
    WHERE emp.empno = i_empno;
END;

/* Value before call */
SELECT comm
  FROM emp
 WHERE empno = 7499
/
/* 300 */

/* Calling Code will now set comm to null */

BEGIN
   -- Call the procedure
   pk_emp.CHANGE (i_empno => 7499, i_comm => NULL);
END;
/

/* Value after call */
SELECT comm
  FROM emp
 WHERE empno = 7499
/
/* NULL */


Steven Feuerstein, PL/SQL Evangelist, Quest Software: "Help me help Oracle improve the PL/SQL language!"


Bryn Llewellyn: "We love to hear from PL/SQL developers. Let us know what is important to you!"

Links

PL/SQL Obsession
Apex Evangelists
O'Reilly Books on Oracle
OTN PL/SQL Best Practices
OTN PL/SQL Page
Steven Feuerstein's Blog