Search This Blog

Wednesday, November 13, 2013

Overloading Subprograms and it's Limitations

The overloading feature in PL/SQL enables you to develop two or more packaged subprograms with the same name. Overloading is useful when you want a subprogram to accept similar sets of parameters that have different data types. For example, the TO_CHAR function has more than one way to be called, enabling you to convert a number or a date to a character string.
PL/SQL allows overloading of package subprogram names and object type methods.
The key rule is that you can use the same name for different subprograms as long as their formal parameters differ in number, order, or data type family.

Consider using overloading when:
  1. Processing rules for two or more subprograms are similar, but the type or number of parameters used varies
  2. Providing alternative ways for finding different data with varying search criteria. For example, you may want to find employees by their employee ID and also provide a way to find employees by their last name. The logic is intrinsically the same, but the parameters or search criteria differ.
  3. Extending functionality when you do not want to replace existing code

You cannot overload(Restrictions):
  1. Two subprograms if their formal parameters differ only in data type and the different data types are in the same family (NUMBER and DECIMAL belong to the same family.)
  2. Two subprograms if their formal parameters differ only in subtype and the different subtypes are based on types in the same family (VARCHAR and STRING are PL/SQL subtypes of VARCHAR2.)
  3. Two functions that differ only in return type, even if the types are in different families
You get a run-time error when you overload subprograms with the preceding features.

No comments: