In 2023 I discovered the Advent of Code and tried out a few of their puzzles. In 2024 I tried more of them. As the name implies, a new puzzle is released each day from December 1st to December 25th. As much as I enjoy puzzles and writing code; I enjoy time with my family even more, so I didn’t do all of them on the day they came out. In fact, as of this writing, I still have a few I’ve yet to even start; but I thought I’d begin to document how I tackled the ones I did solve. Each of my solutions uses SQL and/or PL/SQL.
My Framework
Before I get into each day’s solutions, I’ll lay out my basic framework that I use for all of them.
I have a simple table called ETEXTS with VARCHAR2(50) and CLOB columns, for NAME and TEXT respectively. It is simply a dumping ground for text I parse with a variety of tools.
CREATE TABLE ETEXTS ( NAME VARCHAR2(50 BYTE), TEXT CLOB )
The common tool I use is a package I called ADVENT. It contains types, procedures, and functions that handle common parsing that I do across most, if not all, of the puzzles. Many of the puzzles require reading the text and doing something with each row, so I have a pipelined function, DATA2ROWS, that reads the ETEXTS table for a given day’s data, and returns a collection of VARCHAR2 and INTEGER columns, where each VARCHAR2 is one row and the INTEGER is the row number.
CREATE OR REPLACE TYPE string_order_type AS OBJECT(str VARCHAR2(32767), seq INTEGER); CREATE OR REPLACE TYPE string_order_tab AS TABLE OF string_order_type; CREATE OR REPLACE TYPE vctab AS TABLE OF VARCHAR2(4000); CREATE OR REPLACE TYPE numtab AS TABLE OF NUMBER;
Some puzzles require navigating through a grid or otherwise keeping track of relative horizontal and vertical positions of characters. For these I have DATA2MAP returning a 2-dimensional collection (an associative array of associative arrays) or DATA2CHARMAP returning a collection of characters with their corresponding x and y positions.
The Advent Package
CREATE OR REPLACE PACKAGE advent
IS
TYPE t_maprow IS TABLE OF VARCHAR2(1)
INDEX BY SIMPLE_INTEGER;
TYPE t_map IS TABLE OF t_maprow
INDEX BY SIMPLE_INTEGER;
TYPE charmap_rec IS RECORD
(
c CHAR(1),
x INTEGER,
y INTEGER
);
TYPE charmap_tab IS TABLE OF charmap_rec;
g_map t_map;
FUNCTION data2rows(p_name IN VARCHAR2,
p_filter IN VARCHAR2 DEFAULT NULL,
p_rowdelimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
RETURN string_order_tab
PIPELINED;
FUNCTION data2map(p_name IN VARCHAR2,
p_filter IN VARCHAR2 DEFAULT NULL,
p_rowdelimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
RETURN t_map;
FUNCTION data2charmap(p_name IN VARCHAR2,
p_filter IN VARCHAR2 DEFAULT NULL,
p_rowdelimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
RETURN charmap_tab
PIPELINED;
PROCEDURE display_map(p_map IN t_map);
END;
/
CREATE OR REPLACE PACKAGE BODY advent
IS
FUNCTION data2rows(p_name IN VARCHAR2,
p_filter IN VARCHAR2 DEFAULT NULL,
p_rowdelimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
RETURN string_order_tab
PIPELINED
IS
BEGIN
FOR x IN (
SELECT r.COLUMN_VALUE txt, ROWNUM rn
FROM etexts e, split_clob(e.text, p_rowdelimiter) r
WHERE name = p_name AND (p_filter IS NULL OR REGEXP_LIKE(r.COLUMN_VALUE, p_filter))
)
LOOP
PIPE ROW (string_order_type(x.txt, x.rn));
END LOOP;
RETURN;
END data2rows;
FUNCTION data2map(p_name IN VARCHAR2,
p_filter IN VARCHAR2 DEFAULT NULL,
p_rowdelimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
RETURN t_map
IS
v_map t_map;
v_rowlength INTEGER;
BEGIN
FOR t IN (
SELECT r.COLUMN_VALUE txt, ROWNUM y
FROM etexts e, split_clob(e.text, p_rowdelimiter) r
WHERE name = p_name AND (p_filter IS NULL OR REGEXP_LIKE(r.COLUMN_VALUE, p_filter))
)
LOOP
v_rowlength := LENGTH(t.txt);
FOR x IN 1 .. v_rowlength
LOOP
v_map(x)(t.y) := SUBSTR(t.txt, x, 1);
END LOOP;
END LOOP;
RETURN v_map;
END data2map;
PROCEDURE data2map(p_name IN VARCHAR2,
p_filter IN VARCHAR2 DEFAULT NULL,
p_rowdelimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
IS
BEGIN
g_map := data2map(p_name, p_filter, p_rowdelimiter);
END data2map;
PROCEDURE display_map(p_map IN t_map)
IS
BEGIN
FOR y IN 1 .. p_map(1).COUNT
LOOP
FOR x IN 1 .. p_map.COUNT
LOOP
DBMS_OUTPUT.put(p_map(x)(y));
END LOOP;
DBMS_OUTPUT.new_line;
END LOOP;
END display_map;
FUNCTION data2charmap(p_name IN VARCHAR2,
p_filter IN VARCHAR2 DEFAULT NULL,
p_rowdelimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
RETURN charmap_tab
PIPELINED
IS
BEGIN
FOR t IN (
SELECT r.COLUMN_VALUE txt, ROWNUM y
FROM etexts e, split_clob(e.text, p_rowdelimiter) r
WHERE name = p_name AND (p_filter IS NULL OR REGEXP_LIKE(r.COLUMN_VALUE, p_filter))
)
LOOP
FOR x IN 1 .. LENGTH(t.txt)
LOOP
PIPE ROW (charmap_rec(SUBSTR(t.txt, x, 1), x, t.y));
END LOOP;
END LOOP;
END data2charmap;
END;
/
These routines use another function I wrote about several years ago: split_clob.
Examples
Here are simple examples of each of the data retrieval functions usage:
select * from advent.data2rows('advent2024-1');
select c,x,y from advent.data2charmap('advent2024-10');
DECLARE
v_map advent.t_map := advent.data2map('advent2024-6');
BEGIN
advent.display_map(v_map);
END;
With this framework, I would start each puzzle by copying the puzzle data to my table and then using the advent package, read the data into the most useful form needed for my solution.
In addition to the ADVENT package, I also use some older functions and collection types I’ve used in a variety of other posts as well: vctab, numbtab, str2tbl, str2numtab to parse csv values and return nested tables of varchar2 or number values.
I’ll link an article about each the puzzles I’ve completed successfully below.
I hope they help you look at tricky problems in new ways and maybe inspire you to try the Advent of Code next year yourself!