The ability to create user-defined types in Oracle is very powerful and is supported for many years (since Oracle 8).
It allows us to extend the built-in data types and adjust them to our specific needs, and to use them in our data model and in our code.
In the data model they can be used to define the type of specific columns or the type of tables (“object tables”).
In the code they can be used in SQL statements and in PL/SQL.
I’m not a big fan of using user-defined types in the data model.
I am, however, a huge fan of using them in the code.
Here are a few examples for posts in which user-defined types are used in the code:
So whenever I create a user-defined type my intention is that it will be used only in the code.
If the type is created in a PL/SQL scope, then my intention is enforced by definition.
But what if the type is created in the schema level? Can I enforce my intention? As of version 18c I can.
Persistability
A new feature in Oracle 18c allows to define a user-defined type as either persistable (which is the default) or not persistable.
A persistable type can be used in the code and in the data model, just like any type before 18c.
Let’s create color_t and color_tt as persistable types:
SQL> create type color_t as object ( 2 r number(3), 3 g number(3), 4 b number(3), 5 member function hex_code return varchar2 6 ) 7 / Type created. SQL> SQL> create type body color_t as 2 member function hex_code return varchar2 3 is 4 begin 5 return '#' || to_char(self.r, 'fm0x') || 6 to_char(self.g, 'fm0x') || 7 to_char(self.b, 'fm0x'); 8 end hex_code; 9 end color_t; 10 / Type body created. SQL> create type color_tt as 2 table of color_t 3 / Type created.
We can use persistable types in the data model.
As the type of an object table:
SQL> create table colors of color_t;
Table created.
As the type of a column:
SQL> create table people (
2 first_name varchar2(20),
3 last_name varchar2(30),
4 eye_color color_t
5 );
Table created.
And we can use persistable types in the code.
In PL/SQL:
SQL> declare
2 v_yellow color_t := color_t(255,255,0);
3 v_navy color_t := color_t(0,0,128);
4 begin
5 dbms_output.put_line('Yellow: ' || v_yellow.hex_code);
6 dbms_output.put_line('Navy: ' || v_navy.hex_code);
7 end;
8 /
Yellow: #ffff00
Navy: #000080
PL/SQL procedure successfully completed.
In SQL:
SQL> select *
2 from color_tt(
3 color_t(0,0,0),
4 color_t(255,255,255));
R G B
---------- ---------- ----------
0 0 0
255 255 255
SQL> drop table people; Table dropped. SQL> drop table colors; Table dropped. SQL> drop type color_tt; Type dropped. SQL> drop type color_t; Type dropped.
A non persistable type can be used only in the code.
If we try to use it as the type of an object table or a column, we get the ORA-22384 exception.
Let’s create color_t and color_tt as non-persistable types:
SQL> create type color_t as object ( 2 r number(3), 3 g number(3), 4 b number(3), 5 member function hex_code return varchar2 6 ) 7 not persistable 8 / Type created. SQL> SQL> create type body color_t as 2 member function hex_code return varchar2 3 is 4 begin 5 return '#' || to_char(self.r, 'fm0x') || 6 to_char(self.g, 'fm0x') || 7 to_char(self.b, 'fm0x'); 8 end hex_code; 9 end color_t; 10 / Type body created. SQL> create type color_tt as 2 table of (color_t) 3 not persistable 4 / Type created.
We cannot use non-persistable types in the data model:
SQL> create table colors of color_t; create table colors of color_t * ERROR at line 1: ORA-22384: cannot create a column or table of a non-persistable type SQL> create table people ( 2 first_name varchar2(20), 3 last_name varchar2(30), 4 eye_color color_t 5 ); create table people ( * ERROR at line 1: ORA-22384: cannot create a column or table of a non-persistable type
We can use non-persistable types in the code, in PL/SQL and in SQL:
SQL> declare
2 v_yellow color_t := color_t(255,255,0);
3 v_navy color_t := color_t(0,0,128);
4 begin
5 dbms_output.put_line('Yellow: ' || v_yellow.hex_code);
6 dbms_output.put_line('Navy: ' || v_navy.hex_code);
7 end;
8 /
Yellow: #ffff00
Navy: #000080
PL/SQL procedure successfully completed.
SQL>
SQL> select *
2 from color_tt(
3 color_t(0,0,0),
4 color_t(255,255,255));
R G B
---------- ---------- ----------
0 0 0
255 255 255
SQL> drop type color_tt; Type dropped. SQL> drop type color_t; Type dropped.
Type Dependency
As you may expect, a persistable type cannot depend on non-persistable types.
SQL> create type color_t as object ( 2 r number(3), 3 g number(3), 4 b number(3) 5 ) 6 not persistable 7 / Type created. SQL> create type color_tt as table of color_t 2 / Warning: Type created with compilation errors. SQL> show err Errors for TYPE COLOR_TT: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 ORA-22383: a persistable type cannot have non-persistable attributes
A non-persistable type can depend on both persistable and non-persistable types.
SQL> create or replace type color_tt as
2 table of (color_t)
3 not persistable
4 /
Type created.
Note: the parenthesis are required when creating a collection type with the persistability clause
SQL> drop type color_tt; Type dropped. SQL> drop type color_t; Type dropped.
Type Inheritance
A subtype inherits the persistability attribute of its supertype, and cannot override it.
SQL> create type color_t as object ( 2 r number(3), 3 g number(3), 4 b number(3) 5 ) 6 not final 7 not persistable 8 / Type created. SQL> create type rgba_color_t 2 under color_t 3 (alpha number(3)) 4 persistable 5 / Warning: Type created with compilation errors. SQL> show err Errors for TYPE RGBA_COLOR_T: LINE/COL ERROR -------- ----------------------------------------------------------------- 0/0 PLS-00772: PERSISTABLE or NOT PERSISTABLE not permitted with UNDER clause SQL> create type rgba_color_t 2 under color_t 3 (alpha number(3)) 4 / Type created. SQL> select type_name,persistable 2 from user_types; TYPE_NAME PERSISTABLE -------------------- -------------------- COLOR_T NO RGBA_COLOR_T NO
In the next post I’ll write about an enhancement that was added to non-persistent types in Oracle 21c.
Hi Oran,
I bumped into your article while trying to solve some issues with plsql OOP.
It seems that you just solved it and I think that it explains why Oracle bothered to add the feature in the first place.
Here is the case:
I implemented two levels of hierarchy OOP in plsql when the base type and few under it.
It worked fine as long as one sub-type was initiated and used.
If more than one subtype was initiated and methods invoked it caused a lock.
I couldn’t find any documentation on the issue but I suspected that it has something to do with recompiling the base type behind the scenes and trying to protect consistency of the data model.
Anyhow once I made the base type not persistable the locks disappeared.
I think this is why the feature was introduced in the first place.
Igal, thanks for your comment.
Very interesting.
Oren.