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.