2020 – Part 2 – Find JSON Columns In Describe Function

by

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!

Leave a Comment