I filed a ticket in MOS for this issue; I’ll update this post with the status
In the previous posts I wrote about non-persistable types (a new feature of Oracle 18c), and about the ability to use PL/SQL types as attributes of non-persistable types (a new feature of Oracle 21c).
It seems that the latter is not fully supported yet for type inheritance.
We can define a type with PL/SQL attributes, and then create a sub-type that adds pure SQL attributes. No problem with this, and the PL/SQL attributes are inherited.
21c> create type supertype as object (
2 x pls_integer,
3 y boolean
4 )
5 not final
6 not persistable
7 /
Type created.
21c> create type subtype
2 under supertype (
3 z integer
4 )
5 /
Type created.
21c> declare
2 obj1 subtype;
3 obj2 supertype;
4 begin
5 obj1 := subtype(7, true, 42);
6 obj2 := obj1;
7 dbms_output.put_line(obj2.x);
8 end;
9 /
7
PL/SQL procedure successfully completed.
But if we try to add new PL/SQL attributes when creating a subtype it fails.
21c> create type subtype2 2 under supertype ( 3 z pls_integer 4 ) 5 / Warning: Type created with compilation errors. 21c> show err Errors for TYPE SUBTYPE2: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/7 PLS-00530: Illegal type used for object type attribute: 'PLS_INTEGER'.
My guess is that it happens due to a too strict check in the CREATE TYPE statement – looking for an explicit NOT PERSISTABLE declaration. But it’s impossible to make such a declaration, because, by definition, a subtype inherits the persistability attribute of its supertype, and cannot override it.
21c> create type subtype2 2 under supertype ( 3 z pls_integer 4 ) 5 not persistable 6 / Warning: Type created with compilation errors. 21c> show err Errors for TYPE SUBTYPE2: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 PLS-00772: PERSISTABLE or NOT PERSISTABLE not permitted with UNDER clause