IS OF TYPE throws an exception

Question

I read about IS OF TYPE and I expected it to return TRUE, FALSE or NULL.

I have two types of objects:

CREATE TYPE o1 AS OBJECT ( id NUMBER ); / CREATE TYPE o2 AS OBJECT ( id NUMBER ); / 

When I run the code below, everything is fine.

 DECLARE type1 o1; BEGIN type1 := o1(id=>1); if (type1 IS OF (o1)) then DBMS_OUTPUT.PUT_LINE('type1 is o1'); END if; END; / 

But when I try to run:

 DECLARE type1 o1; BEGIN type1 := o1(id=>1); if (type1 IS OF (o2)) then DBMS_OUTPUT.PUT_LINE('type1 is o1'); END if; END; / 

I got the following exceptions

 Error report: ORA-06550: line 6, column 21: PLS-00382: expression is of wrong type ORA-06550: line 6, column 4: PL/SQL: Statement ignored 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action: 

There is no clear explanation in the documentation, should an exception be thrown if something is wrong? Or if I expect a lie in an IF condition?

Answer 1

If you specified your variable as type O1 , then you can use the is of [type] condition to check if your variable is of type O1 or has a subtype of O1 . Here is an example (variables must be created):

  -- base type SQL> create or replace type o1 as object( 2 prop number 3 )not final; 4 / Type created -- O1 subtype SQL> create or replace type o2 under o1( 2 prop1 number 3 ); 4 / -- test if the l_o1 is of O1 type SQL> declare 2 l_o1 o1; 3 begin 4 l_o1 := o1(prop=>1); 5 if l_o1 is of (o1) 6 then 7 dbms_output.put_line('Yes'); 8 else 9 dbms_output.put_line('No'); 10 end if; 11 end; 12 / Yes PL/SQL procedure successfully completed -- test if the l_o1 is of O2 type SQL> declare 2 l_o1 o1; 3 begin 4 l_o1 := o1(prop=>1); 5 if l_o1 is of (o2) 6 then 7 dbms_output.put_line('Yes'); 8 else 9 dbms_output.put_line('No'); 10 end if; 11 end; 12 / No PL/SQL procedure successfully completed -- test if the l_o2 is of O2 type SQL> declare 2 l_o2 o2; 3 begin 4 l_o2 := o2(prop=>1, prop1 => 1); 5 if l_o2 is of (o2) 6 then 7 dbms_output.put_line('Yes'); 8 else 9 dbms_output.put_line('No'); 10 end if; 11 end; 12 / Yes PL/SQL procedure successfully completed 

Update

Take a look at this for more information on is of[type] . Typically, the data type of a variable is known at compile time, but if you have to deal with dynamic typing, you can look at anydata (the object's data type). Here is a simple example:

  SQL> declare 2 l_o1 o1; 3 4 -- Here is a procedure(for the sake of simplicity has not 5 -- been written as a schema object) 6 -- that doesn't not know 7 -- variable of what dada type will be passed in 8 -- at compile time; 9 procedure DoSomething(p_var anydata) 10 is 11 begin 12 case p_var.gettypename 13 when 'HR.O1' 14 then dbms_output.put_line('O1 data type. Do something'); 15 when 'HR.O2' 16 then dbms_output.put_line('O2 data type. Do something'); 17 else 18 dbms_output.put_line('Unknown data type'); 19 end case; 20 end; 21 22 begin 23 l_o1 := o1(prop => 1); 24 DoSomething(anydata.ConvertObject(l_o1)); 25 end; 26 / O1 data type. Do something PL/SQL procedure successfully completed