TYPE Definition Change in Oracle 21c

SQL*Plus and PL/SQL have been at odds for years -- cases abound where how a task is performed using SQL*Plus differs from how that same or similar task is completed in PL/SQL. Additionally there are data types available in PL/SQL that have been unavailable in SQL*Plus. Oracle version 21c provides a way to use some of the PL/SQL data types in SQL*Plus type definitions, with the caveat that the PL/SQL types will not be persistent. What does this mean and how does one go about using this new-found treasure trove of types? Let's investigate further and see what we find.

For a while now creating certain types in both PL/SQL and SQL*Plus involved a change in syntax -- a type with two elements in PL?SQL is a record, and that same type construction in SQL*Plus becomes an object type. This is something developers have grown accustomed to doing. However if those types need to contain PL/SQL types (boolean, pls_integer, binary_integer, etc.) sadly there was no easy way in SQL*Plus to satisfy that requirement. Until 21c. Let's look at an example of how such types can be used in SQL*Plus type definitions. Let's say, for argument, a type is needed with a BOOLEAN datatype in SQL*Plus. Before 21c it would be necessary to do a LOT of coding to mimic the BOOLEAN data type -- with 21c we can use BOOLEAN as a type, as long as the database doesn't try to persist the data to disk:

SQL> create or replace

  2  type  EMP_SAL_APPROVAL_FOR_PL as object

  3  (  e_num    number,

  4     e_nme    varchar2(35),

  5     new_sal      number,

  6     mgr_approve boolean

  7  ) not persistable ;

  8  /

Type created.

Such an object can be passed to a PL/SQL program unit with the BOOLEAN value at run time and Oracle knows the BOOLEAN value is not to persist outside of the current context. As stated earlier this is not restricted to BOOLEAN values; let's try both a PLS_INTEGER and a BINARY_INTEGER:

SQL> create or replace

  2  type  EMP_SAL_APPROVAL_FOR_PL as object

  3  (  e_num     number,

  4     e_nme     varchar2(35),

  5     new_sal      number,

  6     mgr_approve boolean,

  6     old_sal   pls_integer,

  7     yrly_sal   binary_integer

  8  ) not persistable ;

  9  /

Type created.

Any data type that cannot be persisted in SQL*Plus won't be because of the "not persistable" instruction, although they can be passed to a PL/SQL unit at run time.

If you're wondering if this is also available for use in nested tables and varrays, the answer is yes:

SQL> create or replace

  2  type i_array as

  3  varray(40) of (binary_integer)

  4  not persistable;

  5  /

Type created.

Unfortunately this does not allow using a %TYPE reference (a well-known and often used shortcut with PL/SQL coding to ensure type matching between PL/SQL code and the tables used) in any type using the "not persistable" directive:

SQL> create or replace

  2  type  EMP_SAL_APPROVAL_FOR_PL as object

  3  (  e_num     emp.empno%type,

  4     e_nme     varchar2(35),

  5     new_sal      number,

  6     mgr_approve boolean,

  6     old_sal   pls_integer,

  7     yrly_sal   binary_integer

  8  ) not persistable ;

  9  /

Warning: Type created with compilation errors.

SQL>

SQL> show errors

Errors for TYPE EMP_SAL_APPROVAL_FOR_PL

LINE/COL ERROR

-------- -----------------------------------------------------

0/0      PL/SQL: Compilation unit analysis terminated

3/12     PLS-00201: identifier 'EMP.EMPNO' must be declared

Oracle has made a significant step with the "not persistable" directive, allowing programmers to utilize PL/SQL data types in SQL*Plus type definitions; this will, most likely, have limited use or appeal knowing the data represented by such constructs won't be stored in the database, but when passing BOOLEAN, PL:S_INTEGER or BINARY_INTEGER values from SQL*Plus to PL/SQL program units it can eliminate any type mismatch issues caused by not having such types in SQL*Plus. And this is probably the first step in expanding SQL*Plus functional compatibility with PL/SQL.