I'm not really a Pl/Sql programmer on daily basis anymore, the language is still close to me. And working with object types is a favorite subject to me ever since I discovered the endless use in Oracle 8i. Know Object types and you can do everything in Oracle.
Today I got a comment on the article, asking on if there is a solution on "downcasting" types. The subject in the mentioned article was that from an API (in the example from an EBusiness Suite12 API) you get an object type to which you want to add functionality without re-implementing/changing the delivered object type. The only thing to do it is to sub-type the delivered type. But how to come from an instance of a supertype to the use it as the subtype. Well, the answer is two fold:
- If the instance of the super type is actually instantiated as the supertype then you actually can't. As explained in the mentioned article, the only way is to instantiate the subtype with the attributes of the supertype. For what I think I found a smart generic solution.
- If you get an instance of the supertype, that was actually instantiated as the subtype then you can copy it again to a variable based on the subtypehttp://blog.darwin-it.nl/ and your good to go.
In Java there is a statement called "InstanceOf". In Oracle SQL you have the "Is Of"construct. I found a forum post with the question about an "InstanceOf" analogy in Pl/Sql. As in the forum is stated the "Is Of" clause is in fact a possible where clause condition in a query. See also the documentation.
However in the post I saw a smart comment: create an overloaded procedure:
create procedure p_x(p_obj super_type);Now let's say we have a type named 't_car' and a subtype say 't_opel_zafira under t_car':
do supertype code here
create procedure p_x(p_obj sub_type);
do subtype code here
create type t_car as object (license_plate varchar2(10) ) / create type t_opel_zafira under t_car (model varchar2(10)) /Then you could create an overloaded function like
create function instance_of(p_obj t_car ) returns varchar2 as begin return 't_car'; end; create function instance_of(p_obj t_opel_zafira ) returns varchar2 as begin return 't_opel_zafira'; end;Then you could indeed do something like:
declare l_car t_car; l_opel_zafira t_opel_zafira; begin l_car := instance_opel_zafira_as_car(...); -- This functions returns a value of type t_car but is in fact an Opel Zafira if instance_of(l_car) = 't_opel_zafira' then dbms_output.put_line('Enjoy your Opel Zafira'); end if; end;I should try it, but you should even be able to add a such an instance_of member function to each object type. Going to try that tomorrow.