Posted by There has always been that odd conflict between the language you use for SQL versus the language you use for PL/SQL when it comes to expressing what appears to the be the same thing.
PL/SQL: Do want a datatype that contains two elements? Use a RECORD
SQL> declare
2 type MY_EMPS is record (
3 empno number,
4 ename varchar2(10)
5 );
6 begin
7 null;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL: Do want a datatype that contains two elements? Use an OBJECT TYPE
SQL> create or replace
2 type MY_EMPS as object
3 ( empno number,
4 ename varchar2(10)
5 );
6 /
Type created.
That clash of syntax/styles becomes more apparent when you want to start mixing and matching the datatypes that may or may not be supported in both languages.
PL/SQL: Do want a datatype that contains three elements, one of which is a Boolean? Use a RECORD
SQL> declare
2 type MY_EMPS is record (
3 empno number,
4 ename varchar2(10),
5 approved boolean
6 );
7 begin
8 null;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL: Do want a datatype that contains three elements, one of which is a Boolean?
Well, lets not go there 
Things like the Boolean datatype are one of the reasons why there always been that separation between SQL and PL/SQL when it comes to type definitions. Any datatype you create with the CREATE TYPE command could ultimately become a column in a table or even a table itself under the Object Relational features of the database, and thus anything that was not strictly a SQL datatype was blocked. 18c took a small step toward recognising that not all types were intended for tables and columns when non persistable types where introduced, but we had still the restrictions on the data types available.
21c now recognises that fact. Now when you create a database type and you know it will not be used for any persistence, we have now opened up more options for the definition for that type.
For example, now I can have a Boolean element in my type definition as long as I tell the database I won’t be using the type for persistence.
SQL> create or replace
2 type MY_EMPS_FOR_USE_IN_PLSQL as object
3 ( empno number,
4 ename varchar2(10),
5 approved boolean
6 ) not persistable ;
7 /
Type created.
Or I can use many of the other PL/SQL datatypes that will not normally be allowed in type definition
SQL> create or replace
2 type MY_EMPS_FOR_USE_IN_PLSQL as object
3 ( empno number,
4 ename varchar2(10),
5 approved boolean,
6 small_num pls_integer,
7 big_num binary_integer
8 ) not persistable ;
9 /
Type created.
Non-persistable types are allowed for nested tables and varrays as well
SQL> create or replace
2 type int_list as
3 varray(10) of (pls_integer)
4 not persistable;
5 /
Type created.
But sadly, it looks like it is not possible yet to use reference by %TYPE for the elements.
SQL> desc emp
Name Null? Type
----------------------------- -------- -------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> create or replace
2 type MY_EMPS_FOR_USE_IN_PLSQL as object
3 ( empno emp.empno%type,
4 ename emp.ename%type,
5 approved boolean
6 ) not persistable ;
7 /
Warning: Type created with compilation errors.
SQL>
SQL> sho err
Errors for TYPE MY_EMPS_FOR_USE_IN_PLSQL:
LINE/COL ERROR
-------- -----------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
2/12 PLS-00201: identifier 'EMP.EMPNO' must be declared
In any event, these are nice touches that allow you to bring more consistency to your code base.
If you’re wondering who is the picture, it is George Boole who finally gets his rightful place in database types
Learning is not a spectator sport 