PL/SQL Attributes in User-Defined SUBTYPES

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

Leave a Reply

Your email address will not be published. Required fields are marked *