A script-enabled browser is required for this page to function properly.

PL/SQL conversion utility: Passing NULL to an overloaded subprogram does not resolve properly

In PL/SQL V1, you could define the same subprogram in a package as two different data types: VARCHAR2 and a RECORD type. This is called overloading the subprogram. If you then passed a NULL value to an overloaded subprogram, PL/SQL assumed a data type of VARCHAR2. However, versions after V1, a NULL value passed to an overloaded subprogram within a package must have a defined type.

To preserve V1 behavior, the PL/SQL conversion utility takes one of the following actions, as appropriate, when it encounters a NULL passed to an overloaded subprogram:

  1. Wrap NULL with TO_CHAR( )
  2. Wrap NULL with TO_NUMBER( )
  3. Wrap NULL with TO_DATE( )
  4. Wrap NULL with NOT

If you pass a NULL value to an overloaded program unit without defining the desired data type, the compiler generates this error message:

ERROR 307: Too many declarations of ‘PU name’ match this call . . .

Example

The conversion utility converts this PL/SQL V1 code:

PACKAGE ps1 IS
  TYPE foo IS RECORD(id PLS_INTEGER); Subprogram p is overloaded:
  PROCEDURE p(a VARCHAR2); a is defined as a variable of type VARCHAR2 
  PROCEDURE p(a FOO);  a is also defined as another data type 
END;

PROCEDURE foo2 IS
BEGIN
  ps1.p(NULL); In PL/SQL V1, the VARCHAR2 datatype is assumed.
END;

. . . to this:

PACKAGE ps1 IS
  TYPE foo IS RECORD(id PLS_INTEGER);
  PROCEDURE p(a VARCHAR2);
  PROCEDURE p(a FOO);
END;

PROCEDURE foo2 IS
BEGIN
  ps1.p(TO_CHAR(NULL)); Since a is type VARCHAR2, the NULL
END;                    value is wrapped with TO_CHAR( )

See also

About the PL/SQL conversion utility