oracle_inheritance.sql

CREATE TABLE invoices (
    id          NUMBER PRIMARY KEY,
    strategy_id NUMBER
);

CREATE OR REPLACE TYPE base_strategy AS OBJECT (
   id number,
   not final member procedure do(d date)
) NOT FINAL;
/

CREATE OR REPLACE TYPE BODY base_strategy AS
   not final member procedure do(d date)
   AS
   begin
     raise_application_error(-20001, 'Invalid type usage');
   end;
end;
/

CREATE OR REPLACE TYPE common_strategy under base_strategy (
   OVERRIDING member procedure do(d date)
) FINAL;
/

CREATE OR REPLACE TYPE BODY common_strategy AS
   OVERRIDING member procedure do(d date)
   AS
   begin
     dbms_output.put_line('Common strategy!');
   end;
end;
/

CREATE OR REPLACE TYPE unusual_strategy under base_strategy (
   OVERRIDING member procedure do(d date)
) FINAL;
/

CREATE OR REPLACE TYPE BODY unusual_strategy AS
   OVERRIDING member procedure do(d date)
   AS
   begin
     dbms_output.put_line('Unusual strategy!');
   end;
end;
/

create or replace type invoices_obj as object  (
    id          NUMBER,
    strategy_id NUMBER,
    strategy base_strategy
);
/


insert into invoices(id, strategy_id) values(1, 1);
insert into invoices(id, strategy_id) values(2, 2);

create or replace view invoices_view OF invoices_obj WITH OBJECT IDENTIFIER(id)
as 
select id,
       strategy_id,
       decode(strategy_id, 1,
         common_strategy(strategy_id),
         unusual_strategy(strategy_id))
from   invoices
/

declare
  common_invoice    invoices_obj;
  unusual_invoice   invoices_obj;
begin

  select value(i)
  into   common_invoice
  from   invoices_view i
  where  strategy_id = 1;
  
  
  select value(i)
  into   unusual_invoice
  from   invoices_view i
  where  strategy_id != 1;
  
  common_invoice.strategy.do(sysdate);
  unusual_invoice.strategy.do(sysdate);
end;

/*
Common strategy!
Unusual strategy!
*/