Question: What does it
mean to "overload" an Oracle stored procedure or function? I
hear that overloading has to do with what parameters are passed to
the PL/SQL, but I was hoping for clarification into the nature of
PL/SQL overloading.
Answer: The process of
"overloading" originates in the world of object-oriented
coding with the concept of polymorphism. It is the idea that the functionality of a PL/SQL stored
procedure of function can be changed based on the input datatype.
Polymorphism was a spin off of the PL/SQL concept called
"overloading" Overloading a stored procedure or function
refers to the ability of a programming method to perform more than
one kind of operation depending upon the context in which the method
is used.
For a simple example of overloading, you can
write a PL/SQL function that does one thing when a numeric argument
is passed to the procedure and another thing when a character string
is passed as an argument.
For a simple Oracle example of overloading, consider the
wwv_flow_mail.send procedure. There are two overloaded
versions of the send procedure and Oracle knows which to invoke by
the argument data types passed to the send procedure. If you
pass a CLOB datatype for p_body and p_body_html,
then the second send procedure is invoked.
Polymorphism and overloading in PL/SQL
Polymorphism is the ability of different objects to receive the
same message and respond in different ways. Polymorphism
simplifies the communication between objects by using a common
interface to hide different implementation details.
A very simple example of this is the use of the operator "+", in
working with characters it can be used for concatenation and if used
with numerical values it would be used for addition. A
programming example of overloading would look as follows.
Concatenation example:
First_Name = "John"
Last_Name = "Smith"
Full_Name = First_Name + Last_Name
Numerical example
Kounter = Kounter
+ 1
Oracle member methods and overloading
Overloading is associated with the object-oriented idea of
"polymorphism" whereby a single procedure may be context-sensitive,
depending on the input parameters and it is also used in the
object-oriented Oracle with the idea of member methods.
An
example of this type of method would be an update method that
updates a table attribute based on the value that is passed. If a
date value is passed, the procedure will do the conversion to
character, same with a number.
The technique for
overloading a method procedure or function is identical to the
overloading of standard procedure or function.
create type
test3(
type_passed varchar2(8),
member procedure input_type(in_char
char),
member procedure input_type(in_varchar varchar2),
member procedure input_type(in_date
date),
