SQL & PL/SQL

CREATE OR REPLACE PACKAGE util
AS
  FUNCTION yn (bool IN BOOLEAN)
    RETURN CHAR;

  FUNCTION is_same(a varchar2, b varchar2)
    RETURN BOOLEAN;

  FUNCTION is_same(a date, b date)
    RETURN BOOLEAN;

  /* Oracle's documentation says that you cannot overload subprograms
   * that have the same type family for the arguments.  But, 
   * apparently timestamp and date are in different type families,
   * even though Oracle's documentation says they are in the same one.
   * If we don't create a specific overloaded function for timestamp,
   * and for timestamp with time zone, we get "too many declarations 
   * of is_same match" when we try to call is_same for timestamps.
   */
  FUNCTION is_same(a timestamp, b timestamp)
    RETURN BOOLEAN;

  FUNCTION is_same(a timestamp with time zone, b timestamp with time zone)
    RETURN BOOLEAN;

  /* These two do indeed cause problems, although there are no errors when we compile the package.  Why no errors here? */
  FUNCTION is_same(a integer, b integer) return boolean;

  FUNCTION is_same(a real, b real) return boolean;

END util;
/

CREATE OR REPLACE PACKAGE BODY util
AS
  /********************************************************************************
     NAME: yn
     PURPOSE: pass in a boolean, get back a Y or N
  ********************************************************************************/
  FUNCTION yn (bool IN BOOLEAN)
    RETURN CHAR
  IS
  BEGIN
    IF bool
    THEN
      RETURN 'Y';
    END IF;

    RETURN 'N';
  END yn;

  /********************************************************************************
     NAME: is_same
     PURPOSE: pass in two values, get back a boolean indicating whether they are
              the same.  Two nulls = true with this function.
  ********************************************************************************/
  FUNCTION is_same(a in varchar2, b in varchar2)
    RETURN BOOLEAN
  IS
    bool boolean := false;
  BEGIN
    IF a IS NULL and b IS NULL THEN bool := true;
    -- explicitly set this to false if exactly one arg is null
    ELSIF a is NULL or b IS NULL then bool := false;
    ELSE bool := a = b;
    END IF;
    RETURN bool;
  END is_same;

  FUNCTION is_same(a in date, b in date)
    RETURN BOOLEAN
  IS
    bool boolean := false;
  BEGIN
    IF a IS NULL and b IS NULL THEN bool := true;
    -- explicitly set this to false if exactly one arg is null
    ELSIF a is NULL or b IS NULL then bool := false;
    ELSE bool := a = b;
    END IF;
    RETURN bool;
  END is_same;
  
  FUNCTION is_same(a in timestamp, b in timestamp)
    RETURN BOOLEAN
  IS
    bool boolean := false;
  BEGIN
    IF a IS NULL and b IS NULL THEN bool := true;
    -- explicitly set this to false if exactly one arg is null
    ELSIF a is NULL or b IS NULL then bool := false;
    ELSE bool := a = b;
    END IF;
    RETURN bool;
  END is_same;

  FUNCTION is_same(a in timestamp with time zone, b in timestamp with time zone)
    RETURN BOOLEAN
  IS
    bool boolean := false;
  BEGIN
    IF a IS NULL and b IS NULL THEN bool := true;
    -- explicitly set this to false if exactly one arg is null
    ELSIF a is NULL or b IS NULL then bool := false;
    ELSE bool := a = b;
    END IF;
    RETURN bool;
  END is_same;

  /* Don't bother to fully implement these two, as they'll just cause errors at run time anyway */
  FUNCTION is_same(a integer, b integer) return boolean is begin return false; end;
  FUNCTION is_same(a real, b real) return boolean is begin return false; end;
  
END util;
/

declare
 d1 date := timestamp '2011-02-15 13:14:15';
 d2 date;
 t timestamp := timestamp '2011-02-15 13:14:15';
 t2 timestamp;
 a varchar2(10);
 n real := 1;
 n2 real;
begin
 dbms_output.put_line('dates');
 dbms_output.put_line(util.yn(util.is_same(d2,d2) ));
 dbms_output.put_line(util.yn(util.is_same(d1,d2) ));
 dbms_output.put_line('timestamps'); -- why don't these throw exception?
 dbms_output.put_line(util.yn(util.is_same(t2,t2) ));
 dbms_output.put_line(util.yn(util.is_same(t,t2) ));
 dbms_output.put_line('varchars');
 dbms_output.put_line(util.yn(util.is_same(a,a)));
 dbms_output.put_line(util.yn(util.is_same(a,'a')));
 dbms_output.put_line('numbers');
 -- dbms_output.put_line(util.yn(util.is_same(n,n2))); -- this would throw an exception
end;
/

Originally, I had just the one function with VARCHAR2 arguments. This failed to work properly because when dates were passed in, the automatic conversion to VARCHAR2 was dropping the timestamp. So, I added a 2nd function with DATE arguments. Then I started getting "too many declarations of is_same exist" error when passing TIMESTAMPs. This made no sense to me, so even though Oracle's documentation says you cannot do it, I created a 3rd version of the function, to handle TIMESTAMPS explicitly. Surprisingly, it works fine. But then I noticed it didn't work with TIMESTAMP with TIME ZONEs. Hence, the fourth version of the function. Oracle's docs say that if your arguments are of the same type family, you cannot create an overloaded function, but as the example above shows, this is very wrong.

Lastly, just for grins, I created the two number functions, one with NUMBER, the other with REAL, and even these are allowed - they compile. But then at run time, it fails. I'm really confused.

Here is the apparently incorrect Oracle documentation on the matter: http://docs.oracle.com/cd/B12037_01/appdev.101/b10807/08_subs.htm (see overloading subprogram names), and here are the various types and their families: http://docs.oracle.com/cd/E11882_01/appdev.112/e17126/predefined.htm.

Edited by: hotwater on Jan 9, 2013 3:38 PM

Edited by: hotwater on Jan 9, 2013 3:46 PM