A formatting package needs to format amounts, amounts with a currency, and dates. Instead of format_amount, format_amount_currency, and format_date, overloading lets all three share one name, money, and PL/SQL picks the right version from the arguments of each call.
Code for This Guide
The main examples are in the examples/packages folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
The Rule
Subprograms in the same package, or the same declaration section, can share a name when their parameters differ in number, in order, or in type family. The return type alone does not count.
Example:
function money (p_amount number) return varchar2; function money (p_amount number, p_currency varchar2) return varchar2; function money (p_when date) return varchar2;
One Name, Three Versions
Example:
create or replace package fmt is
function money (p_amount number) return varchar2;
function money (p_amount number, p_currency varchar2) return varchar2;
function money (p_when date) return varchar2;
end fmt;
/
create or replace package body fmt is
function money (p_amount number) return varchar2 is
begin return to_char(p_amount, 'FM999G990D00'); end;
function money (p_amount number, p_currency varchar2) return varchar2 is
begin return p_currency || ' ' || money(p_amount); end;
function money (p_when date) return varchar2 is
begin return to_char(p_when, 'DD Mon YYYY'); end;
end fmt;
/
select fmt.money(1234.5) as a, fmt.money(1234.5, 'AED') as b,
fmt.money(date '2026-03-15') as c;Output:
Package FMT compiled Package Body FMT compiled A B C ___________ _______________ ______________ 1,234.50 AED 1,234.50 15 Mar 2026
The three calls pick the number version, the number-and-currency version, and the date version. The version with a currency calls the version without, so the formatting logic exists once.
When Overloads Are Ambiguous
If two versions differ only in their return type, the package compiles, but no call can choose between them:
Example:
-- two versions that differ only in the return type: no call can choose between them create or replace package fmt2 is function label (p_value number) return varchar2; function label (p_value number) return number; end fmt2; / create or replace package body fmt2 is function label (p_value number) return varchar2 is begin return to_char(p_value); end; function label (p_value number) return number is begin return p_value; end; end fmt2; / select fmt2.label(5) as x from dual;
Output:
Package FMT2 compiled Package Body FMT2 compiled Error starting at line : 12 In command - select fmt2.label(5) as x from dual Error at Command Line : 12 Column : 8 Error report - SQL Error: ORA-06553: PLS-307: too many declarations of 'LABEL' match this call
PLS-00307, too many declarations match this call. The same happens when argument types are in the same family, such as two versions taking NUMBER and INTEGER. The example drops its package afterward.
Things to Know
- Named notation in calls, money(p_when => d), can resolve overloads that positional calls cannot.
- Standalone procedures and functions cannot be overloaded; only subprograms in packages or declaration sections can.
- Many built-in functions are overloaded the same way, such as TO_CHAR for numbers and dates.
Related Guides
- How to Restrict Callers with ACCESSIBLE BY in PL/SQL
- Oracle TO_CHAR (Number) Function: A Simple Guide to Formatting Numbers
Conclusion
Overloading lets package subprograms share a name when their parameters differ in number, order, or type family, and PL/SQL chooses the version from each call's arguments. Keep the versions clearly distinct, since differences in return type alone lead to PLS-00307.
