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_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!

Leave a Reply

Your email address will not be published. Required fields are marked *

× 1 = 9