2020 – Handling Numbers

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_ret_val;
ELSIF v_put_cols(put_col_counter).type = 2 THEN
    := v_ret_val;

That’s it!

The code is here

Thanks for reading!

Leave a Comment