![](\"https:\/\/blog.bar-solutions.com\/img\/mailto.png\")
<\/span>\n<\/td>\n<\/p>\n
Dear Patrick,<\/p>\n
Recently I heard someone talk about overloading in Java. What is it, is it possible in PL\/SQL and if so, how would I use it?<\/p>\n
Ramesh Cumar<\/p>\n
Dear Ramesh,<\/p>\n
Overloading is a technique of creating multiple programs with the same name that can be called with different sets of parameters. It is definitely possible to apply this technique in PL\/SQL, in fact, Oracle does this a lot of times in their own built-in packages. If you take a look at the SYS.STANDARD package then you will find a lot of functions called TO_CHAR, but with different parameter sets. You probably never wondered how Oracle can use the same function name for completely different tasks. It\u2019s just as easy to write
\nTO_CHAR(9) which will result in \u20189\u2019 as it is to write TO_CHAR(SYSDATE) which will result in the current date in to format specified in the NLS_DATE_FORMAT parameter, for example 29-12-15 if the format is \u2018DD-MM-RR\u2019. If you would want to get this value in a different format you can just write TO_CHAR (SYSDATE, \u2018Month, DDth YYYY\u2019) to get \u2018December, 29th 2015\u2019. As you can see they are all calls to a function with the same name, but with completely different sets of parameters.<\/p>\n
This behavior cannot be realized by making all the parameters option, like this:<\/p>\n
FUNCTION TO_CHAR (num_in in number default null\r\n, date_in in date default null\r\n, datemask_in in varchar2) return varchar2;<\/pre>\nBecause if you would want to call this function without using named parameters this call
\nTO_CHAR (SYSDATE) would not work, since SYSDATE returns a DATE and the function expects a number as its first parameter. Maybe it might work, because of the implicit typecasts, but you get the idea.
\nThe way this is implemented is there are multiple functions defined in a package with the same name but different sets of parameters.<\/p>\nOne of the packages you can take a look at, because its implementation is readable, i.e. not wrapped, is the HTP package which you can use to generate HTML output for instance in an APEX application. If you take a look at for instance the PRINT procedure. In the package specification you can see there are three implementations available for this procedure:<\/p>\n
procedure print (cbuf in varchar2 character set any_cs DEFAULT NULL);\r\nprocedure print (dbuf in date);\r\nprocedure print (nbuf in number);<\/pre>\nThe parameters of these function differ not only in name, but also in data type, which is a requirement for the use of overloading:
\nData type and\/or number and\/or name of parameters must differ
\nThe compiler will not complain if you don\u2019t completely comply with this rule, but at runtime you will not be able to use either one of them.<\/p>\nConsider the following package with its implementation<\/p>\n
[PATRICK]SQL>CREATE OR REPLACE PACKAGE ol IS\r\n PROCEDURE p (param_in IN VARCHAR2);\r\n PROCEDURE p (param_in IN CHAR);\r\n END ol;\r\n \/<\/pre>\n[PATRICK]SQL>CREATE OR REPLACE PACKAGE BODY ol IS\r\n PROCEDURE p (param_in IN VARCHAR2)\r\n IS\r\n BEGIN\r\n dbms_output.put_line(param_in);\r\n END p;\r\n PROCEDURE p (param_in IN CHAR)\r\n IS\r\n BEGIN\r\n dbms_output.put_line(param_in);\r\n END p;\r\n END ol;\r\n \/<\/pre>\nIf you want to call the procedure there is no way Oracle can decide which one to use.<\/p>\n
[PATRICK]SQL>BEGIN\r\n ol.p('Hello World');\r\n END;\r\n \/\r\n ol.p('Hello World');\r\n *\r\nERROR at line 2:\r\nORA-06550: Line 2, column 3:\r\nPLS-00307: too many declarations of 'P' match this call.\r\nORA-06550: Line 2, column 3:\r\nPL\/SQL: Statement ignored.<\/pre>\nEven if you were using named parameters you would get the same error. What we have here is so called \u2018ambiguous overloading\u2019. You can read more about this subject at http:\/\/www.stevenfeuerstein.com\/learn\/building-code-analysis.
\nSo, there is definitely a use for overloading but you have to be careful about the parameters, especially when parameters have default values. If you run into a situation of ambiguous overloading you now know why the compiler didn\u2019t complain, but the runtime engine does.<\/p>\nHappy Oracle\u2019ing,
\nPatrick Barel<\/p>\n
| \n<\/span>\n<\/td>\n | \nIf you have any comments on this subject or you have a question you want answered, please send an email to patrick[at]bar-solutions[dot]com<\/a>. If I know the answer, or can find it for you, maybe I can help.<\/span>\n<\/td>\n<\/tr>\n<\/table>\n <\/p>\n |