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:
- Get a column name from the passed in query
- Check if it is in the USER_JSON_DATAGUIDES view
- 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!