2020 – Part 7 – Data Data Data

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!