2020 – Part 8 – Put It All Together

This post finally puts queried data into columns in Polymorphic Table Function (PTF) result set. The algorithm is fairly straightforward:

Get a set of rows (aka ROWSET) using DBMS_TF.GET_ROW_SET
Find the column in the ROWSET that contains JSON
- For each row in the ROWSET
  - Grab the JSON data
    - For each KEY we care about
      - Use JSON_VALUE to politely ask the JSON if it contains the KEY
      - If it does then grab the value and put it in the matching column we have added to the rowset
      - If it doesn’t then no hard feelings – just carry on

Think of it as a game of fish using JSON paths.

Q: Do you have $.KEY1?
A: No. Here is a NULL value instead
Q: Do you have $.KEY2?
A: Yes. Here is it’s value

Here is the code…

-- for every row in the row set...
FOR row_counter IN 1..v_rowcount LOOP 

v_chr_t_counter := 1; 

DECLARE 
  v_whole_json CLOB; 
BEGIN 
  -- 
  -- Get the contents of: 
  -- 1) The current row (row_counter) of... 
  -- 2) The table of CLOB alues (tab_clob) for the... 
  -- 3) Current column (col_counter) in the... 
  -- 4) Fetched row set (v_row_set) 
  -- 
  v_whole_json := 
         v_row_set(col_counter).tab_clob(row_counter); 
  -- 
  -- for every PUT column...
  FOR put_col_counter IN 1..v_put_cols.COUNT LOOP 
    FOR counter2 IN 1..3 LOOP 
      IF counter2 = 2 THEN 
        -- 
        -- Create a JSON_VALUE call like this 
        -- JSON_VALUE(whole JSON, current JSON path ) 
        -- eg: JSON_VALUE('{"KEY1":"VALUE1"}','$.KEY1'); 
        -- JSON does not fail if the path does not exist
        -- 
        DECLARE 
          v_sql VARCHAR2(1000); 
          v_json VARCHAR2(1000) := v_whole_json;
          v_attr VARCHAR2(1000) := v_chr_t(v_chr_t_counter); 
          v_ret_val VARCHAR2(1000); 
        BEGIN 
          v_sql := 'DECLARE q VARCHAR2(1000); 
                    BEGIN q := JSON_VALUE(:1,:2); :3 := q;
                    END;'; 
          EXECUTE IMMEDIATE v_sql USING v_json,
                            v_attr, OUT v_ret_val; 
          -- 
          -- Slide the retrieved value into the... 
          -- 1) Current row (row_counter) of the... 
          -- 2) Table of VARCHAR2 values (tab_varchar2) of the... 
          -- 3) Current put column (counter) 
          -- 
  v_row_set_out(put_col_counter).tab_varchar2(row_counter) 
       := v_ret_val; 
        END; 
      END IF; 
      v_chr_t_counter := v_chr_t_counter + 1;
    END LOOP;
  END LOOP; -- every PUT col 
END;

END LOOP; -- every row of data

Then at the end attach the assembled PUT row set to the fetched GET row set.

DBMS_TF.PUT_ROW_SET(v_row_set_out);

The final code for this whole effort is posted on the Oracle Live SQL site here

Thanks for reading!

Leave a Comment Cancel reply