Category: Oracle18c

DIY ANY_VALUE

I really like the ANY_VALUE aggregate function. But since it was added in Oracle 19c, I can’t use it in my Oracle XE database, as currently the latest version of XE is 18c.
So I decided to implement it as a user-defined function using the Oracle Data Cartridges Interface.

Note that this implementation is for VARCHAR2. If the function is used on other data types, the regular rules for implicit conversion apply.

    
SQL> create type any_value_string_t as object
  2  (
  3      v_value varchar2(4000),
  4      static function odciaggregateinitialize(sctx in out any_value_string_t) return number,
  5      member function odciaggregateiterate
  6      (
  7          self  in out any_value_string_t,
  8          value in varchar2
  9      ) return number,
 10      member function odciaggregatemerge
 11      (
 12          self in out any_value_string_t,
 13          ctx2 in any_value_string_t
 14      ) return number,
 15      member function odciaggregateterminate
 16      (
 17          self        in any_value_string_t,
 18          returnvalue out varchar2,
 19          flags       in number
 20      ) return number
 21  );
 22  /

Type created.

SQL> create type body any_value_string_t as
  2
  3      static function odciaggregateinitialize(sctx in out any_value_string_t) return number is
  4      begin
  5          sctx := any_value_string_t(null);
  6          return odciconst.success;
  7      end;
  8
  9      member function odciaggregateiterate
 10      (
 11          self  in out any_value_string_t,
 12          value in varchar2
 13      ) return number is
 14      begin
 15          if self.v_value is null then
 16              self.v_value := value;
 17          end if;
 18          return odciconst.success;
 19      end;
 20
 21      member function odciaggregateterminate
 22      (
 23          self        in any_value_string_t,
 24          returnvalue out varchar2,
 25          flags       in number
 26      ) return number is
 27      begin
 28          returnvalue := self.v_value;
 29          return odciconst.success;
 30      end;
 31
 32      member function odciaggregatemerge
 33      (
 34          self in out any_value_string_t,
 35          ctx2 in any_value_string_t
 36      ) return number is
 37      begin
 38          if self.v_value is null then
 39              self.v_value := ctx2.v_value;
 40          end if;
 41          return odciconst.success;
 42      end;
 43
 44  end;
 45  /

Type body created.

SQL> begin
  2    $IF DBMS_DB_VERSION.ver_le_18 $THEN
  3      execute immediate q''create function any_value (p_value varchar2) return varchar2
  4                           parallel_enable
  5                           aggregate using any_value_string_t;'';
  6      execute immediate q''grant execute on any_value to public'';
  7      execute immediate q''create public synonym any_value for any_value'';
  8    $ELSE
  9      raise_application_error(-20000,'ANY_VALUE is now supported by Oracle');
 10    $END
 11  end;
 12  /

PL/SQL procedure successfully completed.

Now any_value can be used just like any built-in aggregate function:

SQL> conn hr/hr
Connected.
SQL> select banner from v$version;

BANNER
------------------------------------------------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

1 row selected.

SQL> select d.department_id,
  2         any_value(d.department_name) department_name,
  3         count(*) number_of_employees
  4  from   employees   e,
  5         departments d
  6  where  d.department_id = e.department_id
  7  group  by d.department_id;

DEPARTMENT_ID DEPARTMENT_NAME      NUMBER_OF_EMPLOYEES
------------- -------------------- -------------------
           10 Administration                         1
           20 Marketing                              2
           30 Purchasing                             6
           40 Human Resources                        1
           50 Shipping                              45
           60 IT                                     5
           70 Public Relations                       1
           80 Sales                                 34
           90 Executive                              3
          100 Finance                                6
          110 Accounting                             2

11 rows selected.

Non Persistable Types

The ability to create user-defined types in Oracle is very powerful and is supported for many years (since Oracle 8).
It allows us to extend the built-in data types and adjust them to our specific needs, and to use them in our data model and in our code.
In the data model they can be used to define the type of specific columns or the type of tables (“object tables”).
In the code they can be used in SQL statements and in PL/SQL.

I’m not a big fan of using user-defined types in the data model.
I am, however, a huge fan of using them in the code.

Here are a few examples for posts in which user-defined types are used in the code:

So whenever I create a user-defined type my intention is that it will be used only in the code.
If the type is created in a PL/SQL scope, then my intention is enforced by definition.
But what if the type is created in the schema level? Can I enforce my intention? As of version 18c I can.

Persistability

A new feature in Oracle 18c allows to define a user-defined type as either persistable (which is the default) or not persistable.
A persistable type can be used in the code and in the data model, just like any type before 18c.
Let’s create color_t and color_tt as persistable types:

SQL> create type color_t as object (
  2    r number(3),
  3    g number(3),
  4    b number(3),
  5    member function hex_code return varchar2
  6  )
  7  /

Type created.

SQL>
SQL> 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.

SQL> create type color_tt as
  2    table of color_t
  3  /

Type created.

We can use persistable types in the data model.
As the type of an object table:

SQL> create table colors of color_t;

Table created.

As the type of a column:

SQL> create table people (
  2    first_name varchar2(20),
  3    last_name  varchar2(30),
  4    eye_color  color_t
  5  );

Table created.

And we can use persistable types in the code.
In PL/SQL:

SQL> declare
  2    v_yellow color_t := color_t(255,255,0);
  3    v_navy   color_t := color_t(0,0,128);
  4  begin
  5    dbms_output.put_line('Yellow: ' || v_yellow.hex_code);
  6    dbms_output.put_line('Navy:   ' || v_navy.hex_code);
  7  end;
  8  /
Yellow: #ffff00
Navy:   #000080

PL/SQL procedure successfully completed.

In SQL:

SQL> select *
  2  from color_tt(
  3         color_t(0,0,0),
  4         color_t(255,255,255));

         R          G          B
---------- ---------- ----------
         0          0          0
       255        255        255

SQL> drop table people;

Table dropped.

SQL> drop table colors;

Table dropped.

SQL> drop type color_tt;

Type dropped.

SQL> drop type color_t;

Type dropped.

A non persistable type can be used only in the code.
If we try to use it as the type of an object table or a column, we get the ORA-22384 exception.
Let’s create color_t and color_tt as non-persistable types:

SQL> create type color_t as object (
  2    r number(3),
  3    g number(3),
  4    b number(3),
  5    member function hex_code return varchar2
  6  )
  7  not persistable
  8  /

Type created.

SQL>
SQL> 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.

SQL> create type color_tt as
  2    table of (color_t)
  3  not persistable
  4  /

Type created.

We cannot use non-persistable types in the data model:

SQL> create table colors of color_t;
create table colors of color_t
*
ERROR at line 1:
ORA-22384: cannot create a column or table of a non-persistable type

SQL> create table people (
  2    first_name varchar2(20),
  3    last_name  varchar2(30),
  4    eye_color  color_t
  5  );
create table people (
*
ERROR at line 1:
ORA-22384: cannot create a column or table of a non-persistable type

We can use non-persistable types in the code, in PL/SQL and in SQL:

SQL> declare
  2    v_yellow color_t := color_t(255,255,0);
  3    v_navy   color_t := color_t(0,0,128);
  4  begin
  5    dbms_output.put_line('Yellow: ' || v_yellow.hex_code);
  6    dbms_output.put_line('Navy:   ' || v_navy.hex_code);
  7  end;
  8  /
Yellow: #ffff00
Navy:   #000080

PL/SQL procedure successfully completed.

SQL>
SQL> select *
  2  from color_tt(
  3         color_t(0,0,0),
  4         color_t(255,255,255));

         R          G          B
---------- ---------- ----------
         0          0          0
       255        255        255

SQL> drop type color_tt;

Type dropped.

SQL> drop type color_t;

Type dropped.

Type Dependency

As you may expect, a persistable type cannot depend on non-persistable types.

SQL> create type color_t as object (
  2    r number(3),
  3    g number(3),
  4    b number(3)
  5  )
  6  not persistable
  7  /

Type created.

SQL> create type color_tt as table of color_t
  2  /

Warning: Type created with compilation errors.

SQL> show err
Errors for TYPE COLOR_TT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      ORA-22383: a persistable type cannot have non-persistable
         attributes

A non-persistable type can depend on both persistable and non-persistable types.

SQL> create or replace type color_tt as
  2    table of (color_t)
  3  not persistable
  4  /

Type created.

Note: the parenthesis are required when creating a collection type with the persistability clause

SQL> drop type color_tt;

Type dropped.

SQL> drop type color_t;

Type dropped.

Type Inheritance

A subtype inherits the persistability attribute of its supertype, and cannot override it. 

SQL> create type color_t as object (
  2    r number(3),
  3    g number(3),
  4    b number(3)
  5  )
  6  not final
  7  not persistable
  8  /

Type created.

SQL> create type rgba_color_t
  2    under color_t
  3    (alpha number(3))
  4  persistable
  5  /

Warning: Type created with compilation errors.

SQL> show err
Errors for TYPE RGBA_COLOR_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00772: PERSISTABLE or NOT PERSISTABLE not permitted with
         UNDER clause

SQL> create type rgba_color_t
  2    under color_t
  3    (alpha number(3))
  4  /

Type created.

SQL> select type_name,persistable
  2  from user_types;

TYPE_NAME            PERSISTABLE
-------------------- --------------------
COLOR_T              NO
RGBA_COLOR_T         NO

In the next post I’ll write about an enhancement that was added to non-persistent types in Oracle 21c.

PL/SQL Associative Array Constants

My previous post was about declaring a PL/SQL constant that its type is a PL/SQL record. Today’s post is about declaring a constant that its type is an associative array.

Setup

I’ll extend the example I used in the previous post.
color_t is a record type that represents an RGB color value using 3 bytes (R, G and B).
The get_hex_code function gets a color_t parameter and returns the color’s hexadecimal format.
Here is the package spec:

SQL> create or replace package colors as
  2      subtype byte_t is binary_integer range 0 .. 255 not null;
  3      type color_t is record(
  4          r byte_t default 0,
  5          g byte_t default 0,
  6          b byte_t default 0);
  7      function get_hex_code(i_color in color_t) return varchar2;
  8      procedure print;
  9  end colors;
 10  /

Package created.

I’d like to declare an associative array constant for holding several colors (and then print their hex codes).
The key of the array is the color name and the value is a color_t representing the color RGB triplet.

Pre-18c

In Oracle 12.2 and before we need to write a function and use it for initializing the constant, like init_c_colors in the following example:

SQL> create or replace package body colors as
  2      subtype color_name_t is varchar2(20);
  3      type color_tt is table of color_t index by color_name_t;
  4
  5      function init_c_colors return color_tt;
  6      c_colors constant color_tt := init_c_colors();
  7
  8      function construct_color
  9      (
 10          r in binary_integer default null,
 11          g in binary_integer default null,
 12          b in binary_integer default null
 13      ) return color_t is
 14          v_color color_t;
 15      begin
 16          v_color.r := nvl(construct_color.r, v_color.r);
 17          v_color.g := nvl(construct_color.g, v_color.g);
 18          v_color.b := nvl(construct_color.b, v_color.b);
 19          return v_color;
 20      end construct_color;
 21
 22      function init_c_colors return color_tt is
 23          v_colors color_tt;
 24      begin
 25          v_colors('black') := construct_color(0, 0, 0);
 26          v_colors('white') := construct_color(255, 255, 255);
 27          v_colors('pink') := construct_color(255, 192, 203);
 28          v_colors('yellow') := construct_color(r => 255, g => 255);
 29          v_colors('navy') := construct_color(b => 128);
 30          return v_colors;
 31      end init_c_colors;
 32
 33      function get_hex_code(i_color in color_t) return varchar2 is
 34      begin
 35          return '#' || to_char(i_color.r, 'fm0x') ||
 36                        to_char(i_color.g, 'fm0x') ||
 37                        to_char(i_color.b, 'fm0x');
 38      end get_hex_code;
 39
 40      procedure print is
 41          v_color color_name_t;
 42      begin
 43          v_color := c_colors.first;
 44          while v_color is not null
 45          loop
 46              dbms_output.put_line(rpad(v_color || ':', 8) ||
 47                                   get_hex_code(c_colors(v_color)));
 48              v_color := c_colors.next(v_color);
 49          end loop;
 50      end print;
 51  end colors;
 52  /

Package body created.

SQL> exec colors.print
black:  #000000
navy:   #000080
pink:   #ffc0cb
white:  #ffffff
yellow: #ffff00

PL/SQL procedure successfully completed.

Oracle 18c and Later

According to the Database PL/SQL Language Reference documentation (including the documentation for 18c, 19c and 20c):

When declaring an associative array constant, you must create a function that populates the associative array with its initial value and then invoke the function in the constant declaration.

And this is indeed what we did in the previous example.
But the documentation is outdated. As of Oracle 18c each associative array has a default constructor (the formal name is Qualified Expressions). So we don’t have to write our own constructor function anymore:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> create or replace package colors as
  2      subtype byte_t is binary_integer range 0 .. 255 not null;
  3      type color_t is record(
  4          r byte_t default 0,
  5          g byte_t default 0,
  6          b byte_t default 0);
  7
  8      function get_hex_code(i_color in color_t) return varchar2;
  9      procedure print;
 10  end colors;
 11  /

Package created.

SQL>
SQL> create or replace package body colors as
  2      subtype color_name_t is varchar2(20);
  3      type color_tt is table of color_t index by color_name_t;
  4
  5      c_colors constant color_tt :=
  6                  color_tt('black'  => color_t(0, 0, 0),
  7                           'white'  => color_t(255, 255, 255),
  8                           'pink'   => color_t(255, 192, 203),
  9                           'yellow' => color_t(r => 255, g => 255),
 10                           'navy'   => color_t(b => 128));
 11
 12      function get_hex_code(i_color in color_t) return varchar2 is
 13      begin
 14          return '#' || to_char(i_color.r, 'fm0x') ||
 15                        to_char(i_color.g, 'fm0x') ||
 16                        to_char(i_color.b, 'fm0x');
 17      end get_hex_code;
 18
 19      procedure print is
 20          v_color color_name_t;
 21      begin
 22          v_color := c_colors.first;
 23          while v_color is not null
 24          loop
 25              dbms_output.put_line(rpad(v_color || ':', 8) ||
 26                                   get_hex_code(c_colors(v_color)));
 27              v_color := c_colors.next(v_color);
 28          end loop;
 29      end print;
 30  end colors;
 31  /

Package body created.

SQL> exec colors.print
black:  #000000
navy:   #000080
pink:   #ffc0cb
white:  #ffffff
yellow: #ffff00

PL/SQL procedure successfully completed.

PL/SQL Record Constants

Can we declare a PL/SQL constant that its type is a PL/SQL record?
Since a constant must get its value upon declaration, we have to construct a value of the appropriate type.

Example Setup

Consider the following example.
color_t is a record type that represents an RGB color value using 3 bytes (R, G and B).
The get_hex_code function gets a color_t parameter and returns the color’s hexadecimal format.
I’d like to declare constants for several colors (and then print their hex codes). What should come instead of the question marks?

create or replace package colors as
    subtype byte_t is binary_integer range 0 .. 255 not null;
    type color_t is record(
        r byte_t default 0,
        g byte_t default 0,
        b byte_t default 0);
    function get_hex_code(i_color in color_t) return varchar2;
    procedure print;
end colors;
/

create or replace package body colors as
    c_black  constant color_t := ?;
    c_white  constant color_t := ?;
    c_pink   constant color_t := ?;
    c_yellow constant color_t := ?;
    c_navy   constant color_t := ?;

    function get_hex_code(i_color in color_t) return varchar2 is
    begin
        return '#' || to_char(i_color.r, 'fm0x') || 
                      to_char(i_color.g, 'fm0x') || 
                      to_char(i_color.b, 'fm0x');
    end get_hex_code;

    procedure print is
    begin
        dbms_output.put_line('Black:  ' || get_hex_code(c_black));
        dbms_output.put_line('White:  ' || get_hex_code(c_white));
        dbms_output.put_line('Pink:   ' || get_hex_code(c_pink));
        dbms_output.put_line('Yellow: ' || get_hex_code(c_yellow));
        dbms_output.put_line('Navy:   ' || get_hex_code(c_navy));
    end print;
end colors;
/

Pre-18c

In Oracle 12.2 and before we need to write a function and use it for initializing the constants. Something like construct_color in the following example:

SQL> create or replace package colors as
  2      subtype byte_t is binary_integer range 0 .. 255 not null;
  3      type color_t is record(
  4          r byte_t default 0,
  5          g byte_t default 0,
  6          b byte_t default 0);
  7
  8      -- null parameters mean defaults from color_t
  9      function construct_color
 10      (
 11          r in binary_integer default null,
 12          g in binary_integer default null,
 13          b in binary_integer default null
 14      ) return color_t;
 15
 16      function get_hex_code(i_color in color_t) return varchar2;
 17      procedure print;
 18  end colors;
 19  /

Package created.

SQL>
SQL> create or replace package body colors as
  2      c_black  constant color_t := construct_color(0, 0, 0);
  3      c_white  constant color_t := construct_color(255, 255, 255);
  4      c_pink   constant color_t := construct_color(255, 192, 203);
  5      c_yellow constant color_t := construct_color(r => 255, g => 255);
  6      c_navy   constant color_t := construct_color(b => 128);
  7
  8      function construct_color
  9      (
 10          r in binary_integer default null,
 11          g in binary_integer default null,
 12          b in binary_integer default null
 13      ) return color_t is
 14          v_color color_t;
 15      begin
 16          v_color.r := nvl(construct_color.r, v_color.r);
 17          v_color.g := nvl(construct_color.g, v_color.g);
 18          v_color.b := nvl(construct_color.b, v_color.b);
 19          return v_color;
 20      end construct_color;
 21
 22      function get_hex_code(i_color in color_t) return varchar2 is
 23      begin
 24          return '#' || to_char(i_color.r, 'fm0x') || to_char(i_color.g, 'fm0x') || to_char(i_color.b, 'fm0x');
 25      end get_hex_code;
 26
 27      procedure print is
 28      begin
 29          dbms_output.put_line('Black:  ' || get_hex_code(c_black));
 30          dbms_output.put_line('White:  ' || get_hex_code(c_white));
 31          dbms_output.put_line('Pink:   ' || get_hex_code(c_pink));
 32          dbms_output.put_line('Yellow: ' || get_hex_code(c_yellow));
 33          dbms_output.put_line('Navy:   ' || get_hex_code(c_navy));
 34      end print;
 35  end colors;
 36  /

Package body created.

SQL>
SQL> exec colors.print
Black:  #000000
White:  #ffffff
Pink:   #ffc0cb
Yellow: #ffff00
Navy:   #000080

PL/SQL procedure successfully completed.

Oracle 18c and Later

According to the Database PL/SQL Language Reference documentation (including the documentation for 18c, 19c and 20c):

When declaring a record constant, you must create a function that populates the record with its initial value and then invoke the function in the constant declaration.

And this is indeed what we did in the previous example.
But the documentation is outdated. As of Oracle 18c each PL/SQL record type has a default constructor (the formal name is Qualified Expressions). So we don’t have to write our own constructor function anymore:

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> create or replace package colors as
  2      subtype byte_t is binary_integer range 0 .. 255 not null;
  3      type color_t is record(
  4          r byte_t default 0,
  5          g byte_t default 0,
  6          b byte_t default 0);
  7      function get_hex_code(i_color in color_t) return varchar2;
  8      procedure print;
  9  end colors;
 10  /

Package created.

SQL>
SQL> create or replace package body colors as
  2      c_black  constant color_t := color_t(0, 0, 0);
  3      c_white  constant color_t := color_t(255, 255, 255);
  4      c_pink   constant color_t := color_t(r => 255, g => 192, b => 203);
  5      c_yellow constant color_t := color_t(r => 255, g => 255);
  6      c_navy   constant color_t := color_t(b => 128);
  7
  8      function get_hex_code(i_color in color_t) return varchar2 is
  9      begin
 10          return '#' || to_char(i_color.r, 'fm0x') || to_char(i_color.g, 'fm0x') || to_char(i_color.b, 'fm0x');
 11      end get_hex_code;
 12
 13      procedure print is
 14      begin
 15          dbms_output.put_line('Black:  ' || get_hex_code(c_black));
 16          dbms_output.put_line('White:  ' || get_hex_code(c_white));
 17          dbms_output.put_line('Pink:   ' || get_hex_code(c_pink));
 18          dbms_output.put_line('Yellow: ' || get_hex_code(c_yellow));
 19          dbms_output.put_line('Navy:   ' || get_hex_code(c_navy));
 20      end print;
 21  end colors;
 22  /

Package body created.

SQL>
SQL> exec colors.print
Black:  #000000
White:  #ffffff
Pink:   #ffc0cb
Yellow: #ffff00
Navy:   #000080

PL/SQL procedure successfully completed.

Note that we can use either positional association (the first value is associated with r, the second with g, the third with b)

  2      c_black  constant color_t := color_t(0, 0, 0);
  3      c_white  constant color_t := color_t(255, 255, 255);

or named association

  4      c_pink   constant color_t := color_t(r => 255, g => 192, b => 203);

And since I declared the fields of color_t with 0 as the default value, I can omit some of the associations and the default value will be used for the construction:

  5      c_yellow constant color_t := color_t(r => 255, g => 255);
  6      c_navy   constant color_t := color_t(b => 128);

Yellow: #ffff00
Navy:   #000080

Footnote

As part of the introduction of Qualified Expressions in 18c, a similar enhancement was added to PL/SQL associative arrays. I’ll show this in the next post.