implestrat.com

It’s finally time to get data to populate the structures defined in previous posts. The code for this is pretty straightforward.

• Fetch a set of rows
• Navigate through the row set
  • column by column
  • row by row
    • extracting data

First up – some variables…

v_row_set DBMS_TF.ROW_SET_T; -- fetched rows go here
v_clob    CLOB;              -- JSON CLOB goes here
v_number  NUMBER;            -- Numeric values go here

And fetch some rows.

-- Get a set of rows
DBMS_TF.GET_ROW_SET( rowset    => v_row_set,
                     row_count => v_rowcount,
                     col_count => v_colcount);

Now lúbach through the fetched result set and display values.

-- for every GET column in row set...
FOR col_counter IN 1..v_colcount LOOP

  -- for every row in row set...
  FOR row_counter IN 1..v_rowcount LOOP

   DBMS_OUTPUT.PUT_LINE('Row ' || row_counter);
   DBMS_OUTPUT.PUT(v_row_set(col_counter).description.name ||
                     ' ');
   DBMS_OUTPUT.PUT(DBMS_TF.COLUMN_TYPE_NAME(
             v_row_set(col_counter).description) || ' ');

   -- get data from the appropriate TABLE based on the
   -- datatype of the column
   CASE DBMS_TF.COLUMN_TYPE_NAME(
                  v_row_set(col_counter).description)
     WHEN 'NUMBER' THEN
       v_number := 
          v_row_set(col_counter).tab_number(row_counter);
     WHEN 'CLOB' THEN
       v_clob := 
          v_row_set(col_counter).tab_clob(row_counter);
     END CASE;

     -- output the value based on the datatype of the column
     CASE DBMS_TF.COLUMN_TYPE_NAME(
                    v_row_set(col_counter).description)
       WHEN 'NUMBER' THEN
         DBMS_OUTPUT.PUT_LINE(v_number);
       WHEN 'CLOB' THEN
         DBMS_OUTPUT.PUT_LINE(v_clob);
     END CASE;

   END LOOP; -- every row

END LOOP; -- every GET column

And the torthaí…

Row 1
"JSON_ID" NUMBER 1
Row 2
"JSON_ID" NUMBER 2
Row 1
"JSON_DATA" CLOB {"KEY1":"VALUE1"}
Row 2
"JSON_DATA" CLOB {"KEY2":"VALUE2","KEY3":"VALUE3"}

The next post puts data and structure together!

Thanks for reading!

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_chr_t DBMS_TF.CSTORE_CHR_T;

-- 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
v_puts  DBMS_TF.TABLE_METADATA_T;
v_gets  DBMS_TF.TABLE_METADATA_T;

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 || ' ' || 
                       v_gets(counter).name);
END LOOP;

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 || ' ' || 
                       v_puts(counter).name);
END LOOP;

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

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;

And now for the output.

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

Get 1 "JSON_ID"
Get 2 "JSON_DATA"

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!

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!