2020 – Part 6 – Gets and Puts and CStores

This post recaps all of the pieces we have to put our JSON data into colums in rows. We have the queried (aka GET) columns, the added (aka PUT) columns and the JSON paths.

Here are the declared variables that store all of that information. They are are populated in the DESCRIBE function and then accessed in the FETCH_ROWS procedure as follows:

-- the JSON key name, datatype and path are stored
-- in V_CHR_T - these will eventually be used in
-- JSON_VALUE calls 

-- V_ENV_T will containe the environment of each
-- FETCH_ROWS execution including information
-- about the GET and PUT columns
v_env_t DBMS_TF.env_t;

-- Details such as column name will be stored in
-- V_PUTS and V_GETS

And here is how we get the environment information.

v_env_t := DBMS_TF.GET_ENV;

And output the colum names for the queried – aka GET columns.

v_gets := v_env_t.get_columns;
FOR counter IN 1..v_gets.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE('GET ' || counter || ' ' || 

Then do the same for the added – aka PUT – columns.

v_puts := v_env_t.put_columns;
FOR counter IN 1..v_puts.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE('PUT ' || counter || ' ' || 

And last but not least we output the JSON key name, datatype and path.

FOR counter IN 1..v_chr_t.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE('CHR ' || counter || ' = ' ||

And now for the output.

First up are the columns that came straight from the queried table.

Get 1 "JSON_ID"

And now the columns that we added. They will evenetually hold values extracted from the JSON column named JSON_DATA.

Put 1 "KEY1"
Put 2 "KEY2"
Put 3 "KEY3"
Put 4 "KEY4"

And last but not least – information about the JSON data itself. At this point we will focus on the path values that start with $.

CHR 1 = KEY1
CHR 2 = $.KEY1
CHR 3 = string
CHR 4 = KEY2
CHR 5 = $.KEY2
CHR 6 = string
CHR 7 = KEY3
CHR 8 = $.KEY3
CHR 9 = string
CHR 10 = KEY4
CHR 11 = $.KEY4
CHR 12 = string

We now have all of the prieces we need to complete the result set. If I was to code it using my Imaginary-Conversational-Language (ICL) it would look like this:

  1. I have a rows and columns queried here.
  2. Let me check the enironment. Look’s like it’s KEY1.
  3. Alright. How do I find that in the JSON?
  4. Let me check the CSTORE values. Looks like $.KEY1 will get you there.
  5. Sweet. Hey JSON_VALUE. Get me $.KEY1
  6. Here you go!
  7. Thanks! “VALUE1” – that’s it? Thats some very boring JSON If you ask me.
  8. We didn’t ask.
  9. OK, dont judge me! Whats the next PUT column?

The code for this post is available here

The next post will start putting it together to get and display the JSON values.

Thanks for reading!