Category: Table Functions

To get things up and running the original code for Project 2020 processed string JSON values only. But that is not much use if one has numeric values in their JSON. So I have updated the Polymorphic Table Function (PTF) to handle numbers.

The changes required were very arduous and involved 17 million hours of coding and testing…actually it was easy. Here are the changes:

Add a number column to the result set structure:

WHEN 'number' THEN v_type := DBMS_TF.TYPE_NUMBER;
  v_new_cols(v_col_id) := 
     DBMS_TF.COLUMN_METADATA_T( type    => v_type, 
                                name    => v_colname, 
                                max_len => x.length ); 
  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;

After extracting data with JSON_VALUE calls we need to check the datatype and add to the appropriate PTF table.

IF v_put_cols(put_col_counter).type = 1 THEN
v_row_set_out(put_col_counter).tab_varchar2(row_counter)
  := v_ret_val;
ELSIF v_put_cols(put_col_counter).type = 2 THEN
  v_row_set_out(put_col_counter).tab_number(row_counter)
    := v_ret_val;
END IF;

That’s it!

The code is here

Thanks for reading!

So far the 2020 project code only handles string values such as  {“KEY1″:”VALUE1”} which becomes a column named KEY1 with a value of VALUE1. That’s fine for introductory purposes but what about JSON objects such as {“OBJECT5”:{“KEY5″:”VALUE5”}}?

Thankfully the combination of JSON_DATA_GUIDE, JSON_VALUE and Polymorphic Table Functions (PTF’s) handle’s it easily.

First up is the Data Guide with 2 entries for OBJECT5.

path=$.OBJECT5      type=object length=32
path=$.OBJECT5.KEY5 type=string length=8

Thus the JSON_VALUE calls the 2020 PTF generates will be.

JSON_VALUE(json_data,'$.KEY5')
JSON_VALUE(json_data,'$.OBJECT5.KEY5')

The first will fail silently because that is the default ON ERROR behavior for JSON_VALUE. The second call will successfully find VALUE5 and put it into a column name OBJECT5KEY5.

The PTF code needs a tweak to ignore objects for now. That is done with a CASE statement.

CASE x.type
  WHEN 'string' THEN v_type := DBMS_TF.TYPE_VARCHAR2;
     v_new_cols(v_col_id) := 
       DBMS_TF.COLUMN_METADATA_T( type => v_type,
                                  name => v_colname,
                                  max_len => x.length );
     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;
  ELSE
    NULL;
END CASE;

The working code is available on the Oracle Live SQL site here

Thanks for reading!

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!