Expanded TYPE definitions for PL/SQL 21c

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 Smile

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 Smile

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.