implestrat.com

We are now at the point where our DESCRIBE function parses JSON
to add columns to the result set. For example {“KEY1″:”VAL1”}
adds a column named KEY1 with a datatype of VARCHAR2. The next step in our PTF is actually retrieving the JSON values using JSON paths – for example the path of $.KEY1 will find the values for KEY1 at the JSON root node.

The tricky part is the path is available in our DESCRIBE function
(Compilation Environment) but we need to access it later in the
FETCH_ROWS (Execution Environment) procedure. Thankfully Polymorphic Table Functions (PTF’s) support communication using compilation state variables set in the DESCRIBE function and read in the FETCH_ROWS procedure.

Here are the relevant code snippets from the DESCRIBE function:

Two variables are required:

• • A PLSQL table of Strings as defined in the DBMS_TF package.
    v_chr_t DBMS_TF.CSTORE_CHR_T;
  • Ann index variable for values we put in the PLSQL table
    v_col_info_id NUMBER := 0;

After getting the JSON key name, path and datatype from USER_JSON_DATAGUIDES we add them to the PLSQL table like this:

-- Extract the column name from the path 
-- and then add it to the PLSQL table along with the 
-- path and datatype
  v_colname := REPLACE(x.path,'$','');
  v_colname := REPLACE(v_colname,'.','');
  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;

For example this JSON {“KEY1″:”VAL1”} would create these three entries in the PLSQL table.

v_chr_t(1) = KEY1
v_chr_t(2) = $.KEY1
v_chr_t(3) = string

Then in FETCH_ROWS we retrieve the entries like this:

PROCEDURE fetch_rows IS
  v_chr_t DBMS_TF.CSTORE_CHR_T;
BEGIN
  DBMS_TF.CSTORE_GET(v_chr_t);
  FOR counter IN 1..v_chr_t.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('CHR ' || counter || ' = ' || 
                         v_chr_t(counter));
  END LOOP;
END;

Next time we will retrive the JSON values.

Thanks for reading!

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

The previous post in this series determined which JSON columns have a Dataguide enabled Search Index. This post takes the next step of interrogating the Search Index for the JSON ksys it contains. The interrogation is done via the USER_JSON_DATAGUIDE_FIELDS view. Here’s the code from the PTF’s DESCRIBE function.

FUNCTION describe (p_tbl IN OUT DBMS_TF.TABLE_T )
                   RETURN DBMS_TF.DESCRIBE_T IS
  v_dummy NUMBER;
BEGIN
  -- for every column passed in...
  FOR counter IN 1..p_tbl.column.count LOOP
    -- check if this column has a search index
    BEGIN
      SELECT 1
        INTO v_dummy
        FROM user_json_dataguides
       WHERE column_name = 
TRIM(BOTH '"' FROM p_tbl.column(counter).description.name);
      DBMS_OUTPUT.PUT_LINE(
          p_tbl.column(counter).description.name || 
          'is a JSON Dataguide Field');
      /*
        || Now that we know this column has a dataguide
        || index we can query the dataguide fields view
        || for the actual JSON keys
      */
      FOR x IN ( SELECT *
                   FROM user_json_dataguide_fields
                  WHERE column_name = 
TRIM(BOTH '"' FROM p_tbl.column(counter).description.name)
       ) LOOP
         /*
           || Uitgang JSON key details to the screen
         */
         DBMS_OUTPUT.PUT_LINE('JSON KEY = ' ||
                              x.path || ' ' ||
                              x.type || ' ' ||
                              x.length);
      END LOOP; -- every key in the JSON
    EXCEPTION
      WHEN NO_DATA_FOUND THEN NULL;
    END;
  END LOOP; -- every column passed in
  RETURN(NULL);
END;

And here is the output.

"JSON_DATA"is a JSON Dataguide Field
JSON KEY = $.KEY1 string 8
JSON KEY = $.KEY2 string 8
JSON KEY = $.KEY3 string 8
JSON KEY = $.KEY4 string 8

I promise this is the last postwhere the only result will be Yet Another DBMS_OUTPUT Call – YADC©. The next post will actually add the new columns (KEY1,KEY2,KEY3 and KEY4) to the output!

To me the USER_JSON_DATAGUIDE_FIELDS view is misnamed – it should be named USER_JSON_DATAGUIDE_KEYS because it contains information about KEYS – not FIELDS.

Thanks for reading!

Dont forget the code for all of these posts is available on the Oracle Live SQL site. You can find the links in my twitter @implestrat.