Unanswered: Problem assigning defaults declaration
I am having trouble assigning defaults in the declaration statement of my procedures. Here is an example:
CREATE OR REPLACE PROCEDURE EW_ORDER_TRACKING (ORDER_NUM_V IN NUMBER DEFAULT NULL,
DATE_LOW_V IN DATE DEFAULT '01/01/2001',
DATE_HIGH_V IN DATE DEFAULT TRUNC(SYSDATE))IS
I usually assign NULL to most of these variables to catch any empty strings but in this case I need to default the variables to the given dates. However, no matter what I add as the default, they always seem to take on NULL as their defaults.
SQL> create or replace procedure EW_ORDER_TRACKING (ORDER_NUM_V IN NUMBER DEFAULT NULL,
2 DATE_LOW_V IN DATE DEFAULT '01/01/2001',
3 DATE_HIGH_V IN DATE DEFAULT TRUNC(SYSDATE))IS
The procedure is running using iAS. I have an HTML form accepting variables for the order number and date range. If the user doesn't enter anything for the date do they default to null and over ride the defaults in the declaration? That may be what's happening but I was under the assumption that the defaults would take over in the event of a null entry.
You have hit the nail on the head. If a value is passed for the parameter at all, even a NULL, then it overrides the default. The default only applies if the parameter is actually omitted from the call, as in my example:
SQL> REM Parameters omitted
SQL> exec EW_ORDER_TRACKING (1)
PL/SQL procedure successfully completed.
SQL> REM Parameters passed as NULL
SQL> exec EW_ORDER_TRACKING (1, NULL, NULL)