In the previous post I wrote about non-persistable types – a feature that was added in Oracle 18c and provides a declarative way to prohibit the use of a schema-level user-defined type in the data model and to limit it to the code only.
Oracle 21c adds another enhancement, and allows to use unique PL/SQL predefined types (i.e., BOOLEAN, PLS_INTEGER/BINARY_INTEGER and their predefined subtypes) as attributes of non-persistable schema-level types.
To demonstrate it I’ll change COLOR_T – the type I used in the examples in the previous post. Let’s declare the r, g and b attributes as BINARY_INTEGER instead of NUMBER(3), and add a BOOLEAN attribute – is_favorite.
Trying to do it for a persistable type (in version 21 or before) fails with the PLS-00530 error:
SQL> create type color_t as object (
2 r binary_integer,
3 g binary_integer,
4 b binary_integer,
5 is_favorite boolean,
6 member function hex_code return varchar2
7 )
8 /
Warning: Type created with compilation errors.
SQL> sho err
Errors for TYPE COLOR_T:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5 PLS-00530: Illegal type used for object type attribute:
'BINARY_INTEGER'.
3/5 PLS-00530: Illegal type used for object type attribute:
'BINARY_INTEGER'.
4/5 PLS-00530: Illegal type used for object type attribute:
'BINARY_INTEGER'.
5/15 PLS-00530: Illegal type used for object type attribute:
'BOOLEAN'.
And we’ll get the same error if we try to do it for a non-persistable type in versions before 21c:
19c> create type color_t as object ( 2 r binary_integer, 3 g binary_integer, 4 b binary_integer, 5 is_favorite boolean, 6 member function hex_code return varchar2 7 ) 8 not persistable 9 / Warning: Type created with compilation errors. 19c> show err Errors for TYPE COLOR_T: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/5 PLS-00530: Illegal type used for object type attribute: 'BINARY_INTEGER'. 3/5 PLS-00530: Illegal type used for object type attribute: 'BINARY_INTEGER'. 4/5 PLS-00530: Illegal type used for object type attribute: 'BINARY_INTEGER'. 5/15 PLS-00530: Illegal type used for object type attribute: 'BOOLEAN'.
In Oracle 21c it succeeds:
21c> create type color_t as object ( 2 r binary_integer, 3 g binary_integer, 4 b binary_integer, 5 is_favorite boolean, 6 member function hex_code return varchar2 7 ) 8 not persistable 9 / Type created. 21c> 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.
Using Non-Persistable Types
Non-persistable types, in general, can be used in both PL/SQL and SQL.
But if the type contains unique PL/SQL attributes its usage is restricted to PL/SQL only.
21c> create function f (i_color in color_t) return varchar2 as
2 begin
3 if i_color.is_favorite then
4 return i_color.hex_code;
5 else
6 return 'I do not like this color';
7 end if;
8 end;
9 /
Function created.
21c> declare
2 l_yellow color_t := color_t(255, 255, 0, false);
3 l_navy color_t := color_t(0, 0, 128, true);
4 begin
5 dbms_output.put_line('Yellow: ' || f(l_yellow));
6 dbms_output.put_line('Navy: ' || f(l_navy));
7 end;
8 /
Yellow: I do not like this color
Navy: #000080
PL/SQL procedure successfully completed.
If we try to use it in a SQL statement, we get the ORA-39972 error:
21c> select count(*) from color_tt();
select count(*) from color_tt()
*
ERROR at line 1:
ORA-39972: cannot use an object type with PL/SQL unique attributes in a SQL statement
Using such types in SQL is not supported even where unique PL/SQL types are allowed:
21c> declare
2 l_yellow color_t := color_t(255, 255, 0, false);
3 l_text varchar2(100);
4 begin
5 select f(l_yellow) into l_text from dual;
6 dbms_output.put_line('Yellow: ' || l_text);
7 end f;
8 /
select f(l_yellow) into l_text from dual;
*
ERROR at line 5:
ORA-06550: line 5, column 12:
PL/SQL: ORA-39972: cannot use an object type with PL/SQL unique attributes in a SQL statement
ORA-06550: line 5, column 5:
PL/SQL: SQL Statement ignored
Compare the previous example with the following one – showing that binding unique PL/SQL types to SQL is allowed (as of Oracle 12.1).
21c> create or replace function f (i_bool in boolean) return varchar2 as 2 begin 3 return case i_bool when true then 'Yes' else 'No' end; 4 end f; 5 / Function created. 21c> declare 2 l_true boolean := true; 3 l_text varchar2(100); 4 begin 5 select f(l_true) 6 into l_text 7 from dual; 8 dbms_output.put_line('Do I like this color? ' || l_text); 9 end; 10 / Do I like this color? Yes PL/SQL procedure successfully completed.
Can any predefined PL/SQL type be used?
All the predefined PL/SQL types and subtypes can be used when creating non-persistable collection types (i.e., varrays and nested tables).
However, when creating non-persistable object types, we cannot use subtypes that are restricted with the NOT NULL constraint (like NATURALN or SIMPLE_INTEGER, for example).
The reason is that it is impossible to define attributes of object types as NOT NULL.
21c> create type simple_int_tt as 2 table of (simple_integer) 3 not persistable 4 / Type created. 21c> create type object_t as object ( 2 a binary_integer, 3 b simple_integer, 4 c boolean 5 ) 6 not persistable 7 / Warning: Type created with compilation errors. 21c> show err Errors for TYPE OBJECT_T: LINE/COL ERROR -------- ----------------------------------------------------------------- 3/3 PLS-00218: a variable declared NOT NULL must have an initialization assignment
I think the error message here is a bit misleading, because there is no way to define an initialization assignment