2020 – Part 5 – Passing JSON Details

We are now at the point where our DESCRIBE function parses JSON
to add columns to the result set. For example {“KEY1″:”VAL1”}
adds a column named KEY1 with a datatype of VARCHAR2. The next step in our PTF is actually retrieving the JSON values using JSON paths – for example the path of $.KEY1 will find the values for KEY1 at the JSON root node.

The tricky part is the path is available in our DESCRIBE function
(Compilation Environment) but we need to access it later in the
FETCH_ROWS (Execution Environment) procedure. Thankfully Polymorphic Table Functions (PTF’s) support communication using compilation state variables set in the DESCRIBE function and read in the FETCH_ROWS procedure.

Here are the relevant code snippets from the DESCRIBE function:

Two variables are required:

    • A PLSQL table of Strings as defined in the DBMS_TF package.
      v_chr_t DBMS_TF.CSTORE_CHR_T;
    • Ann index variable for values we put in the PLSQL table
      v_col_info_id NUMBER := 0;

After getting the JSON key name, path and datatype from USER_JSON_DATAGUIDES we add them to the PLSQL table like this:

-- Extract the column name from the path 
-- and then add it to the PLSQL table along with the 
-- path and datatype
  v_colname := REPLACE(x.path,'$','');
  v_colname := REPLACE(v_colname,'.','');
  v_col_info_id := v_col_info_id + 1;
  v_chr_t(v_col_info_id) := v_colname;
  v_col_info_id := v_col_info_id + 1;
  v_chr_t(v_col_info_id) := x.path;
  v_col_info_id := v_col_info_id + 1;
  v_chr_t(v_col_info_id) := x.type;

For example this JSON {“KEY1″:”VAL1”} would create these three entries in the PLSQL table.

v_chr_t(1) = KEY1
v_chr_t(2) = $.KEY1
v_chr_t(3) = string

Then in FETCH_ROWS we retrieve the entries like this:

PROCEDURE fetch_rows IS
  FOR counter IN 1..v_chr_t.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('CHR ' || counter || ' = ' || 

Next time we will retrive the JSON values.

Thanks for reading!