2020 – Part 4 – Add Columns For Scalars In JSON

This post adds columns (finally!) to the result set of the Polymorphic Table Function (PTF). The columns structure (name, datatype and length) is based on the keys in the JSON Data Guide (colname, type and length). Here is the code.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
    RETURN DBMS_TF.DESCRIBE_T IS
  v_dummy    NUMBER;
  v_new_cols DBMS_TF.COLUMNS_NEW_T;
  v_colname  VARCHAR2(100);
  v_col_id   NUMBER := 0;
  v_type     NUMBER;

BEGIN

  /*
   || for every column passed in...
  */
  FOR counter IN 1..p_tbl.column.count LOOP
  BEGIN
    SELECT 1
      INTO v_dummy
      FROM user_json_dataguides
     WHERE column_name = 
TRIM(BOTH '"' FROM p_tbl.column(counter).description.name);

    /*
      || for every key in the JSON...
    */
    FOR x IN ( SELECT *
                 FROM user_json_dataguide_fields
                WHERE column_name = 
TRIM(BOTH '"' FROM p_tbl.column(counter).description.name) ) LOOP

      -- extract column name from the JSON path eg: $.KEY1
      v_colname := REPLACE(x.path,'$','');
      v_colname := REPLACE(v_colname,'.','');
      v_col_id := v_col_id + 1;
      /*
        || Set the column datatype. Only strings for now...
      */
      CASE x.type
        WHEN 'string' THEN v_type := DBMS_TF.TYPE_VARCHAR2;
      END CASE;
      /*
        || Add the new column definition to the list
      */
      v_new_cols(v_col_id) := DBMS_TF.COLUMN_METADATA_T( 
                     type    => v_type,
                     name    => v_colname,
                     max_len => x.length );

      DBMS_OUTPUT.PUT_LINE('Added Column For ' ||
                           x.path || ' ' ||
                           x.type || ' ' ||
                           x.length);

    END LOOP; -- every JSON key
    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
    END;
  END LOOP; -- every column passed in
  -- attach the list of new columns to the cursor
  RETURN DBMS_TF.DESCRIBE_T ( new_columns => v_new_cols );
END;

Also need to add a FETCH_ROWS procedure.

PROCEDURE fetch_rows IS
BEGIN
  NULL;
  -- to avoid ORA-62573: new column (KEY1) is not 
  -- allowed with describe only polymorphic table function
END;

As shown in this result the columns get added but they contain no data.

Added Column For $.KEY1 string 8
Added Column For $.KEY2 string 8
Added Column For $.KEY3 string 8
Added Column For $.KEY4 string 8

KEY1 KEY2 KEY3 KEY4
---- ---- ---- ----

The next begins adding data to the columns by building up the communication between the DESCRIBE function and the FETCH_ROWS procedure.

Thanks for reading!

PS: The code for this post is available on Oracle’s Live SQL site here
PPS: You’ll need the prior code snippets in this feature first. YOu can get to them via my twitter here

Leave a Comment