I remember when I first started getting used to the idea of Oracle referring to functions and procedures collectively as “subprograms”. Nobody else in the industry was using the term. Newbie programmers would ask me, “What’s a subprogram? Is that like a subroutine?” Then there’s the trend of new authors inventing new terminology for old concepts, in order to help sell their programming how-to books. After excitedly purchasing one and reading it over the weekend I said to myself, “I’ve been doing that all along—we just didn’t call it that!” Yet, new terminology slowly seeps into common usage. My favorite pet peeve: using “architect” as a verb. “We architected a new software paradigm.” Sadly, I actually heard a manager say this to another in the hallway. This verbal abuse must end!

Most programmers I’ve met understand the benefits of the pseudo-object-oriented design of packages (encapsulation, data-hiding). And even though Oracle has supported object types since 8i, most of the production systems I’ve worked on since then have rarely made much use of objects, or only use them with collections as a mapping to VB and C# .Net recordsets. They don’t seem to have much use for member functions and procedures, aside from the collection type built-ins. Oracle supports them in schema-level objects as well. Another pet peeve: I can’t extend collection types with my own member subprograms!

 It’s an accepted ideal that programs have high cohesion (grouping closely related things together) and low coupling (minimizing interdependencies between disparate modules). Why then, not our data? The whole point to object-oriented programming is to marry the code directly (or as close to) the data. This makes a great deal of sense when building complex, domain-specific data types and the business logic that manages them. Regardless of whether you store these objects in a hybrid object-relational schema, or flatten them out to be purely relational, the objects themselves in memory can be organized as tightly integrated data structures and the code that operates on them.

I got to thinking about all the various things I typically do with DATE values -- explicit conversions between DATE and VARCHAR2 values (unfortunately, I still see a lot of implicit conversions from date strings to DATE values in production code), truncating to midnight, adding years, months, days, hours minutes or seconds to them, converting to UTC (Coordinated Universal Time), etc. What if instead of building the code and passing data to it, I built the data and passed it the code? This is what object-oriented programming allows us to do. We invoke the member methods (subprograms) of our objects (send them “messages”) and let it transform the data that’s at its core.

A “date” object would have an attribute of DATE, get/set methods, conversion methods and some value transformation methods, for starters:

CREATE OR REPLACE TYPE date_obj AS OBJECT (
   dt DATE
 ,CONSTRUCTOR FUNCTION date_obj(
      p_date_str IN VARCHAR2
     ,p_format_str IN VARCHAR2
   ) RETURN SELF AS RESULT
 ,MEMBER FUNCTION get_date RETURN DATE
 ,MEMBER PROCEDURE set_date(p_date IN DATE)
 ,MEMBER FUNCTION midnight RETURN date_obj
 ,MEMBER FUNCTION add_yrs(p_yrs IN NUMBER) RETURN date_obj
 ,MEMBER FUNCTION add_mos(p_mos IN NUMBER) RETURN date_obj
 ,MEMBER FUNCTION add_days(p_days IN NUMBER) RETURN date_obj
 ,MEMBER FUNCTION add_hrs(p_hrs IN NUMBER) RETURN date_obj
 ,MEMBER FUNCTION add_mins(p_mins IN NUMBER) RETURN date_obj
 ,MEMBER FUNCTION add_secs(p_secs IN NUMBER) RETURN date_obj
 ,MEMBER FUNCTION to_string(p_format_str IN VARCHAR2) RETURN VARCHAR2
);
/

I can always go back later and add things to my object, either via “Create or Replace” or “Alter Cascade” (FORCE was added in 11g). The latter allows me to make changes to the object spec once the object is referenced by other objects and tables.

The get/set methods aren’t strictly required, as object attributes are directly visible and modifiable. However, sometimes we define attributes that are only intended to be operated on programmatically. For these, only a programmer’s convention (an informal “contract” between developers) can deter their misuse. It sure would be nice if I could declare some attributes as private or static (the latter being at the class level, shared for all instances of objects).

Another shortcoming of Oracle’s schema objects is there’s no way to destruct or destroy an object instance after instantiating one, and return its memory to the UGA; at least until it goes out of scope (or you close the session). Then there’s the inability to track your instances of objects – if you lose your reference (by overwriting a local variable with another instance, for example), there’s no way to re-discover it, let alone destroy it. And there’s no automated garbage collection to invoke during your session. However, we may be able to invent some plausible work-arounds or ways to simulate some of these features.

Let’s implement the body now so we can run a few quick tests, to make sure we’re on the right track.

CREATE OR REPLACE TYPE BODY date_obj AS
   CONSTRUCTOR FUNCTION date_obj(
      p_date_str IN VARCHAR2
     ,p_format_str IN VARCHAR2
   ) RETURN SELF AS RESULT
   IS
   BEGIN
     dt := TO_DATE(p_date_str, p_format_str);
     RETURN;
   END date_obj;
   MEMBER FUNCTION get_date RETURN DATE
   IS
   BEGIN
     RETURN(dt);
   END get_date;
   MEMBER PROCEDURE set_date(p_date IN DATE)
   IS
   BEGIN
     dt := p_date;
   END set_date;
   MEMBER FUNCTION midnight RETURN date_obj
   IS
     v_obj date_obj := SELF; -- make a copy
   BEGIN
     v_obj.dt := TRUNC(v_obj.dt);
     RETURN(v_obj);
   END midnight;
   MEMBER FUNCTION add_yrs(p_yrs IN NUMBER) RETURN date_obj
   IS
     v_obj date_obj := SELF; -- make a copy
   BEGIN
     v_obj.dt := ADD_MONTHS(v_obj.dt, p_yrs * 12);
     RETURN(v_obj);
   END add_yrs;
   MEMBER FUNCTION add_mos(p_mos IN NUMBER) RETURN date_obj
   IS
     v_obj date_obj := SELF; -- make a copy
   BEGIN
     v_obj.dt := ADD_MONTHS(v_obj.dt, p_mos);
     RETURN(v_obj);
   END add_mos;
   MEMBER FUNCTION add_days(p_days IN NUMBER) RETURN date_obj
   IS
     v_obj date_obj := SELF; -- make a copy
   BEGIN
     v_obj.dt := v_obj.dt + p_days; -- default unit for date arithmetic
     RETURN(v_obj);
   END add_days;
   MEMBER FUNCTION add_hrs(p_hrs IN NUMBER) RETURN date_obj
   IS
     v_obj date_obj := SELF; -- make a copy
   BEGIN
     v_obj.dt := v_obj.dt + p_hrs/24; -- #hrs in a day
     RETURN(v_obj);
   END add_hrs;
   MEMBER FUNCTION add_mins(p_mins IN NUMBER) RETURN date_obj
   IS
     v_obj date_obj := SELF; -- make a copy
   BEGIN
     v_obj.dt := v_obj.dt + p_mins/1440; -- #mins in a day
     RETURN(v_obj);
   END add_mins;
   MEMBER FUNCTION add_secs(p_secs IN NUMBER) RETURN date_obj
   IS
     v_obj date_obj := SELF; -- make a copy
   BEGIN
     v_obj.dt := v_obj.dt + p_secs/86400; -- #secs in a day
     RETURN(v_obj);
   END add_secs;
   MEMBER FUNCTION to_string(p_format_str IN VARCHAR2) RETURN VARCHAR2
   IS
   BEGIN
     RETURN(TO_CHAR(dt, p_format_str));
   END to_string;
END;
/

Run a quick test:

declare
 v_obj date_obj := date_obj(sysdate); -- default constructor
begin
 dbms_output.put_line('v_obj.dt=[' ||
    to_char(v_obj.get_date, 'YYYY-MM-DD HH24:MI:SS') || ']');
 v_obj := v_obj.midnight().add_yrs(1).add_mos(-1).add_days(1).
             add_hrs(1).add_mins(1).add_secs(1);
 dbms_output.put_line('v_obj.dt=[' ||
    v_obj.to_string('YYYY-MM-DD HH24:MI:SS') || ']');
end;
/
v_obj.dt=[2013-03-13 05:32:27]
v_obj.dt=[2014-02-14 01:01:01]

You can see after truncating the current date to midnight, I successfully added one year, subtracted a month, and added one day, hour, month and second.

Some notes:

I made a copy of the object and manipulated that, rather than itself. Why? If I try to write to its attribute I get a compilation error:

   MEMBER FUNCTION midnight RETURN date_obj
   IS
   BEGIN
     dt := trunc(dt); -- write to itself
     RETURN(v_obj);
   END midnight;
 
[Error] PLS-00363 (45: 6): PLS-00363: expression 'SELF.DT' cannot be used as an assignment target

The first implicit parameter to a member function is SELF, the object itself. The implicit parameter is defined as “SELF IN ”. I can of course provide it explicitly, and make the mode IN OUT.

   MEMBER FUNCTION midnight(SELF IN OUT date_obj) RETURN date_obj
   IS
   BEGIN
     dt := trunc(dt); -- write to itself
     RETURN(SELF);
   END midnight;

This does compile (after changing it in the spec too). What happens when I run it?

declare
 v_obj date_obj := date_obj(sysdate); -- default constructor
begin
 dbms_output.put_line('v_obj.dt=[' ||
    to_char(v_obj.get_date, 'YYYY-MM-DD HH24:MI:SS') || ']');
 v_obj := v_obj.midnight();
 dbms_output.put_line('v_obj.dt=[' ||
    v_obj.to_string('YYYY-MM-DD HH24:MI:SS') || ']');
end;
/
v_obj.dt=[2013-03-13 18:40:20]
v_obj.dt=[2013-03-13 00:00:00]

That actually works! Let’s try another one of these functions and see how well they chain.

   MEMBER FUNCTION add_yrs(SELF IN OUT date_obj, p_yrs IN NUMBER)
  RETURN date_obj
   IS
   BEGIN
     dt := ADD_MONTHS(dt, p_yrs * 12);
     RETURN(SELF);
   END add_yrs;
 
declare
 v_obj date_obj := date_obj(sysdate); -- default constructor
begin
 dbms_output.put_line('v_obj.dt=[' ||
    to_char(v_obj.get_date, 'YYYY-MM-DD HH24:MI:SS') || ']');
 v_obj := v_obj.midnight();
 v_obj := v_obj.midnight().add_yrs(1);
 dbms_output.put_line('v_obj.dt=[' ||
    v_obj.to_string('YYYY-MM-DD HH24:MI:SS') || ']');
end;
/

Crash and burn! I can’t modify the object’s own attribute and chain it for the next method call. I can’t have it both ways. I think calling each method separately in sequence is rather clumsy; I’d rather be able to chain and assign, as I had it earlier.

Notice I made the data type for each add_* function a NUMBER and not an INTEGER. There’s nothing wrong with adding a third of a day, or a quarter of an hour:

declare
 v_obj date_obj := date_obj(sysdate); -- default constructor
begin
 dbms_output.put_line('v_obj.dt=[' ||
    to_char(v_obj.get_date, 'YYYY-MM-DD HH24:MI:SS') || ']');
 v_obj := v_obj.midnight().add_days(1/3).add_hrs(1/4);
 dbms_output.put_line('v_obj.dt=[' ||
    v_obj.to_string('YYYY-MM-DD HH24:MI:SS') || ']');
end;
/
v_obj.dt=[2013-03-13 19:00:49]
v_obj.dt=[2013-03-13 08:15:00]

I think this just lends itself to extra flexibility.

Speaking of flexibility, I think it’d be nice to be able to alter the session’s NLS date format, and then have that as the default for my constructor’s p_format_str parameter. I think this would also be an appropriate time to introduce a supporting package for my object type.

First I’ll add these static subprograms ahead of my constructor:

   ,STATIC PROCEDURE set_default_format(p_format_str IN VARCHAR2)
    ,STATIC FUNCTION get_default_format RETURN VARCHAR2

Then I’ll modify my supplied constructor to this:

 ,CONSTRUCTOR FUNCTION date_obj(
      p_date_str IN VARCHAR2
     ,p_format_str IN VARCHAR2 DEFAULT date_obj.get_default_format()
   ) RETURN SELF AS RESULT

I may as well use it in my to_string function too:

 ,MEMBER FUNCTION to_string(p_format_str IN VARCHAR2
                               DEFAULT date_obj.get_default_format())
   RETURN VARCHAR2

Notice I had to qualify the static function reference with the object’s name, and provide empty parentheses for the would-be parameter list.

So what’s the difference between a member function and a static function? The member function operates on this instance of an object, while the static function operates at the object (class) level itself. So once I set the NLS date format at the class level, its value is immediately available to all instances of the class, via the static function. This makes sense, since the NLS date format is set for the session.

Here’s my supporting package:

-- "library" package to support date_obj and related things
CREATE OR REPLACE PACKAGE date_obj_lib IS
 PROCEDURE set_default_format(p_format_str IN VARCHAR2);
 FUNCTION get_default_format RETURN VARCHAR2;
END date_obj_lib;
/
 
CREATE OR REPLACE PACKAGE BODY date_obj_lib IS
 PROCEDURE set_default_format(p_format_str IN VARCHAR2)
 IS
    PRAGMA AUTONOMOUS_TRANSACTION; -- don't hose current transaction
 BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''' ||
                      p_format_str || '''';
 END set_default_format
 ;
 FUNCTION get_default_format RETURN VARCHAR2
 IS
 BEGIN
    RETURN(SYS_CONTEXT('USERENV','NLS_DATE_FORMAT'));
 END get_default_format;
END date_obj_lib;
/
I can go back later on and add packaged functions for all of my add_* member functions to use, instead of embedding the “business” logic in the object (regardless of how trivial it may seem) on the theory that my application code can make use of these subprograms directly out of the package, as well as via my object – again, added flexibility. You never know what calling context will use these things. Tomorrow it might be a Java or external C# module. Your packages are a core part of your application’s framework.
 

Sidebar: Look into my crystal ball!

At one shop, the director angrily called me into his office, demanding to know why I had “wasted” an entire week building (and documenting) a server application (written in Pro*/C and using Oracle Pipes) for a COBOL-based address normalization server, and a PL/SQL package interface to the Pro*/C server (again, via Pipes), when all I needed was to link the COBOL shared-object library directly into my C batch program. I calmly explained that I was already leveraging the address normalization functionality from not just C but also in several PL/SQL applications, and in fact the Cold Fusion developers were now making use of it as well (after reading my excellent documentation). So the effort was being repaid multiple times over.

The director said, “Oh, OK. Good work.”

Once the package header is compiled, I can reference the packaged subprograms from my object type body:

   STATIC PROCEDURE set_default_format(p_format_str IN VARCHAR2)
   IS
   BEGIN
     date_obj_lib.set_default_format(p_format_str);
   END set_default_format;
   STATIC FUNCTION get_default_format RETURN VARCHAR2
   IS
   BEGIN
     RETURN(date_obj_lib.get_default_format);
   END get_default_format;
   CONSTRUCTOR FUNCTION date_obj(
      p_date_str IN VARCHAR2
     ,p_format_str IN VARCHAR2 DEFAULT date_obj.get_default_format()
   ) RETURN SELF AS RESULT
   IS
   BEGIN
     dt := TO_DATE(p_date_str, p_format_str);
     RETURN;
   END date_obj;
 
   . . .
 
   MEMBER FUNCTION to_string(p_format_str IN VARCHAR2
                               DEFAULT date_obj.get_default_format())
   RETURN VARCHAR2
   IS
   BEGIN
     RETURN(TO_CHAR(dt, p_format_str));
   END to_string;

Let’s run a test:

DECLARE
 v_obj date_obj; -- uninitialized; non-object
BEGIN
 date_obj_lib.set_default_format('DD-MON-RR'); -- default
 DBMS_OUTPUT.put_line(date_obj.get_default_format());
 v_obj := date_obj ('01-MAR-13'); -- use package default
 DBMS_OUTPUT.put_line(v_obj.to_string (NULL)); -- display using default
 
 date_obj.set_default_format('MM/DD/YYYY'); -- set new default
 v_obj := NEW date_obj('03/Ol/20l3'); -- keyword "NEW" is syntactic sugar
 DBMS_OUTPUT.put_line(v_obj.to_string('YYYY-MM-DD')); -- display alt format
 DBMS_OUTPUT.put_line(date_obj_lib.get_default_format);
 
 date_obj.set_default_format('YYYY-MM-DD HH24:MI:SS'); -- new default
  v_obj := date_obj (DATE '2013-03-14'); -- this uses default constructor
 DBMS_OUTPUT.put_line(v_obj.to_string);
 DBMS_OUTPUT.put_line(date_obj_lib.get_default_format);
 
 v_obj := date_obj (); -- initialize to now
 DBMS_OUTPUT.put_line(v_obj.to_string);
 DBMS_OUTPUT.put_line(date_obj_lib.get_default_format);
 
 v_obj := date_obj (SYSDATE); -- initialize to now; uses default constructor
 DBMS_OUTPUT.put_line(v_obj.to_string);
 DBMS_OUTPUT.put_line(date_obj_lib.get_default_format);
 
END; 
/
DD-MON-RR
01-MAR-13
2013-03-01
MM/DD/YYYY
2013-03-14 00:00:00
YYYY-MM-DD HH24:MI:SS
2013-03-15 09:41:34
YYYY-MM-DD HH24:MI:SS
2013-03-15 09:41:34
YYYY-MM-DD HH24:MI:SS

One question you might as is why my object type spec calls its own date_obj.get_default_format() rather than the packaged one directly. I did this to reduce unnecessary coupling. Having the object type spec referencing the package spec directly resulted in an odd circular reference later on, when I was adding a packaged function that returned a collection type based on the object type:

Then whenever I compiled the package spec, it invalidated the object spec, which cascaded into invalidating the package spec! So by pushing the reference into the object type body, I eliminated this dependency.

It might seem on the surface like a lot of code overhead for one small object. But the code in the object body and package is reentrant – only one copy of it in memory across the entire system – so in fact this is a very efficient model. It’s an unfortunate common occurrence to see essentially the same code repeated in many different packages, just because it’s a very useful routine. The proper thing to do is refactor the code to break it out of the many occurrences and replace with many references to a single location. But nobody has the time to go back and do it right, and so the bad design pattern (or anti-pattern) repeats itself. Like any disease, if you catch it quickly enough (“hey why is the same parse routine in three different packages?”) you can eradicate it before it gets any worse.