implestrat.com

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.

The first posts in this series set up the data, the Polymorphic Table Function (PTF) and the Dataguide Search Index. This posts starts putting it all together by finding the JSON columns passed into the PTF.

Its done in the DESCRIBE function with this code:

-- for every column passed in
FOR counter IN 1..p_tbl.column.count LOOP
  -- is this a JSON Data Guide column?
  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');
  EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;
  END; -- JSON column
END LOOP; -- every column passed in

Its pretty straightforward:

1. Get a column name from the passed in query
2. Check if it is in the USER_JSON_DATAGUIDES view
3. If it is then output a string

I’ll expand the functionality in future posts but right now I want to make an important point – I assume the query passed in belongs to the current schema by querying the USER view. Will that always be valid? I dont know yet – but I will cover it in future posts.

Another point I want to make is that column names have quotes around them as shown in the output from running the PTF.

"JSON_DATA"is a JSON Dataguide Field

In the next post we’ll start extracting the KEY’s from the JSON.

Thanks for reading!