DROP TABLE imgs_flat_test PURGE;

CREATE TABLE imgs_flat_test
(
    img_id NUMBER,
    pix_id NUMBER,
    pix_val NUMBER
);

CREATE OR REPLACE DIRECTORY D1 AS '/tmp';

DECLARE
    f_imgs               UTL_FILE.file_type;
    l_buffer             RAW (32);
    l_cnt                NUMBER := 0;
    l_pix_id             NUMBER := 0;
    l_img_id             NUMBER := 0;
    l_eof                BOOLEAN := FALSE;

    TYPE t_imgs_flat_test IS TABLE OF imgs_flat_test%ROWTYPE
        INDEX BY BINARY_INTEGER;

    arr_imgs_flat_test   t_imgs_flat_test;
BEGIN
    f_imgs := UTL_FILE.fopen ('D1', 't10k-images-idx3-ubyte', 'rb');

    FOR i IN 1 .. 4
    LOOP
        UTL_FILE.get_raw (f_imgs, l_buffer, 4);
        DBMS_OUTPUT.put_line (
            UTL_RAW.cast_to_binary_integer (l_buffer,
                                            endianess   => UTL_RAW.big_endian));
    END LOOP;

    LOOP
        l_cnt := l_cnt + 1;
        l_pix_id := MOD (l_cnt, 28 * 28);

        IF l_pix_id = 0
        THEN
            l_pix_id := 784;
        END IF;

        l_img_id := CEIL (l_cnt / (28 * 28));

        BEGIN
            UTL_FILE.get_raw (f_imgs, l_buffer, 1);
            arr_imgs_flat_test (l_cnt).img_id := l_img_id;
            arr_imgs_flat_test (l_cnt).pix_id := l_pix_id;
            arr_imgs_flat_test (l_cnt).pix_val :=
                UTL_RAW.cast_to_binary_integer (
                    l_buffer,
                    endianess   => UTL_RAW.big_endian);
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                l_eof := TRUE;
        END;

        IF MOD (l_cnt, 1e6) = 0 OR l_eof
        THEN
            FORALL i IN arr_imgs_flat_test.FIRST .. arr_imgs_flat_test.LAST
                INSERT INTO imgs_flat_test
                VALUES arr_imgs_flat_test (i);

            arr_imgs_flat_test.delete;

            COMMIT;
        END IF;

        IF l_eof
        THEN
            EXIT;
        END IF;
    END LOOP;

    COMMIT;
END;
/

DROP TABLE imgs_test PURGE;

CREATE TABLE imgs_test
(
    img_id    NUMBER PRIMARY KEY
);

BEGIN
    FOR i IN 1 .. 28 * 28
    LOOP
        EXECUTE IMMEDIATE 'alter table IMGS_TEST add (p' || i || ' number)';
    END LOOP;
END;
/

DECLARE
    l_pivot_clause   VARCHAR2 (32000);
BEGIN
    FOR i IN 1 .. 28 * 28
    LOOP
        l_pivot_clause := l_pivot_clause || i || ' as P' || i || ',';
    END LOOP;

    l_pivot_clause := RTRIM (l_pivot_clause, ',');

    EXECUTE IMMEDIATE 'INSERT INTO IMGS_TEST
    SELECT *
      FROM (
        SELECT a.IMG_ID,
               a.PIX_ID,
               a.pix_val / 255 pix_val
          FROM imgs_flat_test a
      )
           PIVOT
               (MAX (pix_val) FOR pix_id IN (' || l_pivot_clause || '))';

    COMMIT;
END;
/

DROP TABLE imgs_test_val;

CREATE TABLE imgs_test_val
(
    img_id    NUMBER PRIMARY KEY,
    img_val NUMBER
);

DECLARE
    f_imgs              UTL_FILE.file_type;
    l_buffer            RAW (32);
    l_cnt               NUMBER := 0;
    l_val               NUMBER := 0;

    TYPE t_imgs_test_val IS TABLE OF imgs_test_val%ROWTYPE
        INDEX BY BINARY_INTEGER;

    arr_imgs_test_val   t_imgs_test_val;
BEGIN
    f_imgs := UTL_FILE.fopen ('D1', 't10k-labels-idx1-ubyte', 'rb');

    FOR i IN 1 .. 2
    LOOP
        UTL_FILE.get_raw (f_imgs, l_buffer, 4);
        DBMS_OUTPUT.put_line (
            UTL_RAW.cast_to_binary_integer (l_buffer,
                                            endianess   => UTL_RAW.big_endian));
    END LOOP;

    LOOP
        l_cnt := l_cnt + 1;

        BEGIN
            UTL_FILE.get_raw (f_imgs, l_buffer, 1);
            arr_imgs_test_val (l_cnt).img_id := l_cnt;
            arr_imgs_test_val (l_cnt).img_val :=
                UTL_RAW.cast_to_binary_integer (
                    l_buffer,
                    endianess   => UTL_RAW.big_endian);
        EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
                EXIT;
        END;
    END LOOP;

    FORALL i IN arr_imgs_test_val.FIRST .. arr_imgs_test_val.LAST
        INSERT INTO imgs_test_val
        VALUES arr_imgs_test_val (i);

    COMMIT;
END;
/

CREATE OR REPLACE VIEW mnist_test_set
AS
    SELECT to_char(img_val) img_lbl,
           b.*
      FROM imgs_test_val a, imgs_test b
     WHERE a.img_id = b.img_id;       

ALTER VIEW mnist_test_set ADD CONSTRAINT test_set_pk PRIMARY KEY (img_id)
                             DISABLE NOVALIDATE;