A FAQ that I get, well, frequently, is about overloading procedures. I think most people get how to overload but not when or why. Today I am going to explain a classic example of where overloading makes your life easier. More importantly, it makes the life of the code maintainer behind you easier.

I am going to use the sample data on the HR schema for this entry. Specifically, the EMPLOYEES table.

In real life, I have had to dump table to text files so many times I can't count them. I have also had to format data for reports and interfaces and such. The common thread with these activities is that I need to convert the data to a string to make it useful wherever it is going.

To keep this simple, I am just going to dump three fields from the EMPLOYEES table: first_name, hire_date and salary. That will cover three basic data types: varhcar2, date and number. I will also limit myself to 3 records.

If I was dealing with a query (instead of a table) that was producing 500 columns for a CSV file, in addition to knowing which column had which data type, I would have to know which columns to surround with quotes. That means I would not be able to embed the to_char in the query, I would need to perform that logic in the code. My procedure would then change to:

That's still not so bad but it is only a single table and three columns. Imagine it as a query involving multiple tables and many columns. If coding that is mind numbing and full of despair, can you imagine the guy who comes along two years later and has to maintain that steaming pile of PL/SQL? Arghhh!!!

I am going to create a package to help do this. I will call the package STRINGER.

A package definition does not get much easier than that. I will have a function for each data type that I need to convert. I don't really need a VARCHAR2 function, but if I don't have one, I have to know what the data types are coming in and I am right back where I was before.

There is no longer any reason for us to be concerned with data types. It is still pretty ugly, though, with the double quotes. I also don't like the fact that we have a hard-coded date format.

I will change the package to allow setting a global date format for use by the package as well as adding function to comma separate appropriately. I'm going by the rule that strings are quoted but dates and numbers are not.

You can also call the string functions directly from your SQL if you are writing an online interface. I prefer to leave SQL uncorrupted and do this kind of formatting via PL/SQL but that is just a personal choice.

I hope this helps explain why and when you might use overloading. BTW, this code should also compile and run in EnterpriseDB.

Some name

Lewis is an Oracle Ace Director, Oracle Certified Professional, published author, frequent conference speaker and Database Architect. Lewis's specialties revolve around databases, data warehousing, business intelligence and most anything having to do with databases. He has two decades of multi-vendor and open source database experience in a variety of industries and capacities and has worked with Oracle since 1993.