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.