Prior to use APEX_JSON.GET_VARCHAR2(), need to parse APEX_JSON.PARSE() within a PL/SQL code. Rather than using PL/SQL, SQL might be used directly through use of XMLTABLE such as
WITH t1 AS
(
SELECT APEX_JSON.TO_XMLTYPE(jsdata) AS xml_data
FROM t0 -- suppose your json data is inserted into jsdata column of this table
), t2 AS
(
SELECT close_reason,
ROW_NUMBER() OVER
(ORDER BY TO_TIMESTAMP(closed_at, 'yyyy-mm-dd"T"hh24:mi:ss.ff"Z"') DESC) AS rn
FROM t1
CROSS JOIN
XMLTABLE('/json/openings/row'
PASSING xml_data
COLUMNS
closed_at VARCHAR2(100) PATH 'closed_at',
close_reason VARCHAR2(900) PATH 'close_reason/name'
)
)
SELECT close_reason
FROM t2
WHERE rn = 1
in order to bring the latest close reason.
If you really need to use those functions given in the question, then use the following code block
DECLARE
v_json VARCHAR2(32767);
v_ct OWA.VC_ARR;
v_cr OWA.VC_ARR;
TYPE ts IS TABLE OF TIMESTAMP INDEX BY BINARY_INTEGER;
v_ts ts;
v_ts1 TIMESTAMP;
idx INT;
BEGIN
SELECT *
INTO v_json
FROM t0; -- there's no WHERE clause assuming only one row is to be inserted
APEX_JSON.PARSE(v_json);
FOR i IN 1..APEX_JSON.GET_COUNT('openings')
LOOP
v_ct(i) := APEX_JSON.GET_VARCHAR2('openings['||i||'].closed_at');
v_ts(i) := TO_TIMESTAMP(v_ct(i), 'yyyy-mm-dd"T"hh24:mi:ss.ff"Z"');
IF NVL(v_ts1, v_ts(i)) <= v_ts(i) THEN
v_ts1 := v_ts(i);
idx := i;
END IF;
v_cr(i) := APEX_JSON.GET_VARCHAR2('openings['||i||'].close_reason.name');
END LOOP;
DBMS_OUTPUT.PUT_LINE(v_cr(idx));
END;
/