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.

David Fitzjarrell
David Fitzjarrell has more than 20 years of administration experience with various releases of the Oracle DBMS. He has installed the Oracle software on many platforms, including UNIX, Windows and Linux, and monitored and tuned performance in those environments. He is knowledgeable in the traditional tools for performance tuning – the Oracle Wait Interface, Statspack, event 10046 and 10053 traces, tkprof, explain plan and autotrace – and has used these to great advantage at the U.S. Postal Service, American Airlines/SABRE, ConocoPhilips and SiriusXM Radio, among others, to increase throughput and improve the quality of the production system. He has also set up scripts to regularly monitor available space and set thresholds to notify DBAs of impending space shortages before they affect the production environment. These scripts generate data which can also used to trend database growth over time, aiding in capacity planning. He has used RMAN, Streams, RAC and Data Guard in Oracle installations to ensure full recoverability and failover capabilities as well as high availability, and has configured a 'cascading' set of DR databases using the primary DR databases as the source, managing the archivelog transfers manually and montoring, through scripts, the health of these secondary DR databases. He has also used ASM, ASMM and ASSM to improve performance and manage storage and shared memory.

Latest Articles