Triggered by a comment on an older article I wrote a little article yesterday on determining the instance of an object. As far as known by me at least and seemingly by Google there is no java instanceOf counterpart in Oracle Pl/Sql. In SQL Where clauses you can use the 'is of' expression. But that is not available in pl/sql. I tried some suggestions with embedded sql but I couldn't get it to work. The suggestion from a forum I found yesterday was to use function overloading. I played around a little with it and it seems I stumbled on a fairly simple method. Let's say I've a type like the following:

CREATE OR REPLACE TYPE "DWN_PERSON" AS OBJECT 
( 
name varchar2(40)
, member function instance_of return varchar2
) not final;
/
CREATE OR REPLACE TYPE BODY "DWN_PERSON" AS
  member function instance_of return varchar2 AS
  BEGIN
    return 'DWN_PERSON';
  END instance_of;
END;
/

I just created a simple Instance_of method that does nothing but returning the name of the object. It was my first step in making things spiffier later on. Keeping in mind that it probably should become an overloading function with a parameter. Then I created a Natural Person type:

  CREATE OR REPLACE TYPE "DWN_NATURAL_PERSON" under DWN_PERSON 
( surname varchar2(100)
, overriding member  function instance_Of return varchar2 
) ;
/
CREATE OR REPLACE TYPE BODY "DWN_NATURAL_PERSON" AS
  overriding member  function instance_Of return varchar2 AS
  BEGIN
    RETURN 'DWN_NATURAL_PERSON';
  END instance_Of;
END;
/

And because I was going so well, I created a Not Natural Person:

CREATE OR REPLACE TYPE "DWN_NOT_NATURAL_PERSON" under DWN_PERSON 
( companyname varchar2(100)
, overriding member  function instance_Of return varchar2 
) ;
/
CREATE OR REPLACE TYPE BODY "DWN_NOT_NATURAL_PERSON" AS
  overriding member  function instance_Of return varchar2 AS
  BEGIN
    RETURN 'DWN_NOT_NATURAL_PERSON';
  END instance_Of;
END;
/

Then I created the following little script:

declare
l_person1 dwn_person;
l_person2 dwn_person;
l_person3 dwn_person;

function create_person(p_name varchar2, p_surname varchar2, p_comany_name varchar2) return dwn_person
as
l_person dwn_person;
begin
  if p_surname is not null then
  l_person := dwn_natural_person(p_name, p_surname);
  elsif p_comany_name is not null then
    l_person := dwn_not_natural_person(p_name, p_comany_name);
  else
  l_person := dwn_person(p_name);
  end if;
 return l_person;
end;

begin
  l_person1 := create_person('Flip', 'Fluitketel', null);
  dbms_output.put_line('l_person1 is a '||l_person1.instance_of);
  l_person2 := create_person('Hatseflats', null, null);
  dbms_output.put_line('l_person2 is a '||l_person2.instance_of);
  l_person3 := create_person('Hatseflats', null, 'Hatseflats Inc.');
  dbms_output.put_line('l_person3 is a '||l_person3.instance_of);
end;

All three persons are declared as a 'DWN_PERSON'. So when I call the instance_of method of the particular object I expected that the method of the particular declared type is called. But, a little to my surprise, it turns out that Oracle uses the method of the Instantiated type:

l_person1 is a DWN_NATURAL_PERSON
l_person2 is a DWN_PERSON
l_person3 is a DWN_NOT_NATURAL_PERSON

Simple, but apparently very effective.