Solving Advent of Code in Oracle

In 2023 I discovered the Advent of Code and tried out a few of their puzzles. In 2024 I decided to try to solve all 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 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;

Some puzzles require navigating through a grid or otherwise keeping track of relative horizontal and vertical positions of characters. For these I have routines to return a 2-dimenstional collection (an associative array of associative arrays) or a collection of character position records.

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_rowdelimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
        RETURN string_order_tab
        PIPELINED;

    FUNCTION data2map(p_name IN VARCHAR2, p_rowdelimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
        RETURN t_map;

    FUNCTION data2charmap(p_name IN VARCHAR2, 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_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
                 )
        LOOP
            PIPE ROW (string_order_type(x.txt, x.rn));
        END LOOP;

        RETURN;
    END;

    FUNCTION data2map(p_name IN VARCHAR2, 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
                 )
        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;

    PROCEDURE data2map(p_name IN VARCHAR2, p_rowdelimiter IN VARCHAR2 DEFAULT CHR(13) || CHR(10))
    IS
    BEGIN
        g_map := data2map(p_name, p_rowdelimiter);
    END;

    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;

    FUNCTION data2charmap(p_name IN VARCHAR2, 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
                 )
        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;
END;
/

These routines use another function I wrote about several years ago: split_clob.

Examples

Here are a couple of simple examples of the most common functions usage:

select * from advent.data2rows('advent2024-1');

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.

Below are the puzzles I’ve completed successfully thus far.
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!