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.

Can anyone tell me what I'm doing wrong here?

Thanks

It works for me:

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
4
5 begin
6 dbms_output.put_line(DATE_LOW_V);
7 dbms_output.put_line(DATE_HIGH_V);
8 end;
9 /

Procedure created.

SQL> exec EW_ORDER_TRACKING (1)
01/01/2001
13/11/2003

PL/SQL procedure successfully completed.

However, I would amend the DATE_LOW_V parameter as follows:

DATE_LOW_V IN DATE DEFAULT TO_DATE('01/01/2001','DD/MM/YYYY'),

otherwise you are dependent on the NLS_DATE_FORMAT:

SQL> alter session set nls_date_format='DD-MON-YYYY';

Session altered.

SQL> exec EW_ORDER_TRACKING (1)
begin EW_ORDER_TRACKING (1); end;

*
ERROR at line 1:
ORA-01843: not a valid month
ORA-06512: at line 1

Thank Tony..

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.

What do you think?

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)
01/01/2001
13/11/2003

PL/SQL procedure successfully completed.

SQL> REM Parameters passed as NULL
SQL> exec EW_ORDER_TRACKING (1, NULL, NULL)



PL/SQL procedure successfully completed.