Chris (and anyone else who might still be interested in this thread, despite the long silence since the last post on this topic),

Below is an universal sollution to distinguish between the default NULL or passed NULL in a parameter of any datatype. The sollution seems somehow clumsy, but this is as close as I could get.

The main idea: instead of setting null value as the default parameter value, we rather call a function which sets this default null value to the parameter, but at the same time this function also buffers the information that parameter is null because no value was passed to it. This buffer can later be inspected inside the called procedure or function.

The functions for setting default NULLs to the parameters of three main data types are packed inside the package called DEFAULT_NULL. For buffering information which parameters were set to null because of the default value I used system package DBMS_APLICATION_INFO. With this package we can easily read and write inter-session information, but the main drawback is that the length of such session specific information is limited to 64 characters. So this approach might not be usable on procedures or functions with very high number of parameters with the default values (the upper limit of such parameters would be around 30). As a delimiter between the refferences to different parameters I used character '#'.

So here is the package DEFAULT_NULL:

CREATE OR REPLACE PACKAGE default_null AS
--FUNCTION date_null (p_name VARCHAR2) RETURN DATE;
--FUNCTION varchar2_null (p_name VARCHAR2) RETURN VARCHAR2;
--FUNCTION number_null (p_name VARCHAR2) RETURN NUMBER;
END default_null;
/

CREATE OR REPLACE PACKAGE BODY default_null AS
--PROCEDURE append_info (p_info VARCHAR2) IS
----v_current_info VARCHAR2(64);
--BEGIN
----DBMS_APPLICATION_INFO.READ_CLIENT_INFO(v_current_info);
----DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NVL(v_current_info, '#') || p_info || '#');
--END v_current_info;

--FUNCTION date_null(p_name VARCHAR2) RETURN DATE IS
--BEGIN
----append_info (p_name);
----RETURN NULL;
--END date_null;

--FUNCTION varchar2_null(p_name VARCHAR2) RETURN VARCHAR2 IS
--BEGIN
----append_info (p_name);
----RETURN NULL;
--END varchar2_null;

--FUNCTION number_null(p_name VARCHAR2) RETURN NUMBER IS
--BEGIN
----append_info (p_name);
----RETURN NULL;
--END number_null;
END default_null;
/

And here is a procedure FOO which sets default NULLs by calling DEFAULT_NULL's functions:

CREATE OR REPLACE PROCEDURE FOO
--(p_value1 IN DATE := default_null.date_null('P1'),
---p_value2 IN NUMBER := default_null.number_null('P2')
--)
AS
--v_client_info VARCHAR2(64);
BEGIN
--IF p_value1 IS NULL THEN
----DBMS_APPLICATION_INFO.READ_CLIENT_INFO(v_client_info);
----IF INSTR(v_client_info, '#P1#') > 0 THEN
------DBMS_OUTPUT.PUT_LINE ('Value1 IS NULL by DEFAULT');
----ELSE
------DBMS_OUTPUT.PUT_LINE('Value1 IS NULL');
----END IF;
--END IF;
--IF p_value2 IS NULL THEN
----IF INSTR(v_client_info, '#P2#') > 0 THEN
------DBMS_OUTPUT.PUT_LINE ('Value2 IS NULL by DEFAULT');
----ELSE
------DBMS_OUTPUT.PUT_LINE('Value2 IS NULL');
----END IF;
--END IF;
--DBMS_APPLICATION_INFO.SET_CLIENT_INFO(NULL); -- Clear the buffered information
--NULL; -- "True" procedure logic follows
END;

Here are some different calls to this procedure and its output from SQL*Plus:

SQL> EXEC foo(sysdate, 1)
PL/SQL procedure successfully completed.

SQL> EXEC foo
Value1 IS NULL by DEFAULT
Value2 IS NULL by DEFAULT
PL/SQL procedure successfully completed.

SQL> EXEC foo(NULL, NULL)
Value1 IS NULL
Value2 IS NULL
PL/SQL procedure successfully completed.

SQL> EXEC foo(NULL, 1)
Value1 IS NULL
PL/SQL procedure successfully completed.

SQL> EXEC foo(sysdate, NULL)
Value2 IS NULL
PL/SQL procedure successfully completed.

SQL> EXEC foo(NULL)
Value1 IS NULL
Value2 IS NULL by DEFAULT
PL/SQL procedure successfully completed.

SQL> EXEC foo(p_value2 => NULL)
Value1 IS NULL by DEFAULT
Value2 IS NULL
PL/SQL procedure successfully completed.

HTH,