Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

EnterpriseDB: Code Overloading Oracle Tips by Burleson

There is a feature of EnterpriseDB, inherited from PostgreSQL, that is a good thing but will be very non-intuitive to Oracle developers.  It kind of goes against some of the basic principles of PL/SQL coding.

That feature is code overloading.  In EnterpriseDB SPL, you can overload stand-alone functions.  In Oracle PL/SQL, you must package your functions (and procedures) to overload them.

An overloaded stored function is a function with the same name but a different signature.  For example,

CREATE OR REPLACE FUNCTION abc( p_num IN NUMBER ) RETURN VARCHAR2
AS
BEGIN
  RETURN 'This is a number function';
END;

CREATE OR REPLACE FUNCTION abc( p_char IN VARCHAR2 ) RETURN VARCHAR2
AS
BEGIN
  RETURN 'This is a character function';
END;

In EnterpriseDB, this is considered an overloaded function.  I will call these functions:

edb=# select abc(999);

            abc
---------------------------
 This is a number function

(1 row)

edb=# select abc('lewis');

             abc
------------------------------
 This is a character function

(1 row)

In Oracle, the second function would overwrite the first function.  If I compile and execute these in Oracle, I get a different result:

SQL> select abc(999) from dual;

ABC(999)
---------------------------------------------
This is a character function

SQL> select abc('Lewis') from dual; 

ABC('LEWIS')
---------------------------------------------
This is a character function

It is a subtle difference but one that can surprise you.  To overload these two functions in PL/SQL (and which I can also do in SPL), I would put them in a package:

CREATE OR REPLACE PACKAGE overloaded
AS 

  FUNCTION abc( p_num IN NUMBER ) RETURN VARCHAR2; 

  FUNCTION abc( p_char IN VARCHAR2 ) RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY overloaded
AS 

  FUNCTION abc( p_num IN NUMBER ) RETURN VARCHAR2
  AS
  BEGIN
    RETURN 'This is a number function';
  END;

  FUNCTION abc( p_char IN VARCHAR2 ) RETURN VARCHAR2
  AS
  BEGIN
    RETURN 'This is a character function';
  END;
END;

This code compiles in both EnterpriseDB and Oracle and when run returns identical results. 

Another reason to beware of overloading without packages is that not only can SPL be overloaded in EnterpriseDB, so can any of the other languages.  Make sure that you are really calling the function that you think you are. 

In non-SPL languages, you may only be passing one of two parameters and assume that the second parameter is overloaded.  That would be a bad assumption.  In reality, there will be two functions with two different parameter signatures.  If it is doing what you want, that's a good thing.  If it is not or you are getting errors, verify that you are calling what you think you are.

The third and final reason that it is confusing is that overloaded functions in Developer Studio are not displayed properly.  For example, if we look in Developer Studio for our newly created functions (the ABC functions above) in Figure 8.1 below:

Figure 8.1: Developer Studio Overloaded Function

As you can see, it appears that there is only a single abc function.  However, if you look in Navicat for PostgreSQL (Figure 8.2), you will see both functions:

Figure 8.2: Navicat Overloaded Function Display

If I dropped the VARCHAR2 version of abc, I would then see the NUMBER version of ABC, but until then, I would just be confused.  I am sure the Developer Studio issue will be fixed in a future version.

This feature is not so much an incompatibility issue as it is a training issue.  The point to remember is that overloading is possible without packages in EnterpriseDB Advanced Server.

CODE Compatibility

This section will outline a list of items that you will run into problems with if you have expectations that they will be available.  I fully expect all of these issues to be resolved at some point in time but currently, they are an issue and are something anyone porting an application must be aware of.

This list is not exhaustive.  I doubt I have tried to execute every piece of functionality much less discovered every missing feature or bug. 

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.