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; 

  v_whole_json CLOB; 
  -- 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 := 
  -- 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
          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); 
          v_sql := 'DECLARE q VARCHAR2(1000); 
                    BEGIN q := JSON_VALUE(:1,:2); :3 := q;
          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_ret_val; 
      END IF; 
      v_chr_t_counter := v_chr_t_counter + 1;
  END LOOP; -- every PUT col 

END LOOP; -- every row of data

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


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

Thanks for reading!