Fun with Colors
The fun with colors carries us back to Crayola’s and refrigerator art. One thing I liked about my ole Crayola crayons is that they each came with their name stamped on the side: brick red, mulberry, and pine green. With a very simple table structure, we Convert RGB to Hex with PLSQL. With small effort, you can display these colors in your applications. So why not have a color table? Then you too can flow color name to hex or RGB colors on demand within an Oracle APEX application.
You don’t need much. I opted to store the RGB values and a name. The resultant table has five fields:
- Color primary key
- Color Name
- Red Value (0-255)
- Green Value (0-255)
- Blue Value (0-255)
CREATE TABLE "BLOG_COLOR" ( "COLOR_PK" NUMBER(11,0), "COLOR_NAME" VARCHAR2(60), "COLOR_RED" NUMBER(3,0), "COLOR_GREEN" NUMBER(3,0), "COLOR_BLUE" NUMBER(3,0) );
Convert RGB to Hex with PLSQL
Converting red, green, blue color scheme to hex using Oracle PL/SQL is a formula. The first step is to explore how the to_char() function converts a decimal number to a hexadecimal number.
The formula is: to_char(15,’XXX’). Give it a go in your favorite PL/SQL interface.
select to_char(15,'XXX') red from dual;
To go the other way around: hex to decimal?
select to_number('F','XX') red from dual;
Display colors through HTML is often easier with six-character long representation of the color value.
Firebrick is RGB(178, 34, 34) or #B22222. Hex B2 is 178. And hex value for decimal 22 is 34.
Hexadecimal colors are really concatenations of 3 hex values: 2 for red, 2 for green, and 2 for blue. The firebrick in hex is B2 – 34 -34. Keeping track of your digits is important. Darkgreen which is RGB(0,100,0) must be represented as #006400 in hex. The digits are significant. So you’ll need to trim spaces and add your padding.
select lpad(trim(to_char(15,'XXX')),2,'0') red from dual;
Read more about lpad at Oracle’s site. Here we are stating that the result must be 2 characters long and be padded with zeros.
select '#' || lPad(trim(to_char(:red,'XXX')),2,'0') || lPad(trim(to_char(:green,'XXX')),2,'0') || lPad(trim(to_char(:blue,'XXX')),2,'0') HEX_COLOR from dual;
For my values, I used 178, 34, 34 (firebrick red). My query yielded: ‘#B22222’.
Back to APEX
The image shown is a snippet of a color table viewed as a classic report in Oracle Application Express. As shown above, the hex value for the color is result of a formula within the query (calculated column). The sample column is a blending of two tricks. I primed my query with a simple Font Awesome shape to display my color swatch. And then I used two column in my HTML formatting for the classic report column.
HTML Format Report Column
Just to finish it off, here is the query.
select color_name "Color", color_red "Red", color_green "Green", color_blue "Blue", '#' || lPad(trim(to_char(color_red,'XXX')),2,'0') || lPad(trim(to_char(color_green,'XXX')),2,'0') || lPad(trim(to_char(color_blue,'XXX')),2,'0') HEX_COLOR, 'fa-square' square from tg_colors
Converting RGB to Hex with PLSQL may not be as much fun as a box of Crayola crayons and big pieces of paper. But it is better than stepping outside in a blustery and snowy May 15th!