PLSQL JSON Parsing 3

The previous post in this series moved recursively through a JSON structure to list all keys and structure types. The code worked but the output was an ugly concatenated string. This post shows how PL/SQL table functions can make it more useful.

I’ll post the code separately. Here are the important snippets.

-- Create a database type (object) that defines
-- the rows to return
CREATE OR REPLACE TYPE v_vc2_t AS TABLE OF VARCHAR2(100);
/

-- set the return type of the function to the above object
CREATE OR REPLACE FUNCTION what_are_you
 ( p_json CLOB )
 RETURN v_vc2_t IS
...
 -- Assemble rows as the function executes
 v_ret_val.EXTEND;
 v_ret_val(v_ret_val.LAST) := 
       v_key_list(counter) || ' ' ||
       v_object.get_type(v_key_list(counter));
...
-- Return the set of assembled rows
 RETURN(v_ret_val);
...
END;
/
-- the function is run like this
DECLARE
 v_json CLOB;
BEGIN
 v_json := '{"Name" : "Marty",
 "Reason" : [ { "Medium" : "Movie",
 "Title" : "Madagascar",
 "Year" : 2005 },
 { "Medium" : "Movie",
 "Title" : "Madagascar Escape 2 Africa",
 "Year" : 2008 },
 { "Medium" : "Movie",
 "Title" : "Madagascar 3 : Europes Most Wanted",
 "Year" : 2012 } ] }';
 -- SELECT rows from the function and display them
 FOR x IN (
     SELECT *
       FROM TABLE(what_are_you(v_json)) ) LOOP
   DBMS_OUTPUT.PUT_LINE(x.column_value);
 END LOOP;
END;

And the results look like this:

Name SCALAR
Reason ARRAY
Medium SCALAR
Title SCALAR
Year SCALAR
Medium SCALAR
Title SCALAR
Year SCALAR
Medium SCALAR
Title SCALAR
Year SCALAR

That output looks a lot better and can be easily processed. The code is available here.

Thanks for reading!

Segue From Table Functions To JSON SQL

Its been a while since my last post. There are two reasons for that; one – I’ve said all I wanted to about table functions and such and two – I’ve started on a new direction (tangent?). ¬†JavaScript Object Notation (JSON) and SQL. This post is my segue from the table functions series into a JSON SQL series.

-- create a table with a JSON column
CREATE TABLE json_table
( json_column CLOB,
 CONSTRAINT must_be_json
 CHECK ( json_column IS JSON ) );

-- insert some json data
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "1 and 2"} }');
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "2 and 2"} }');
INSERT INTO json_table
VALUES('{"fieldone" : { "fieldtwo" : "3 and 2"} }');

-- create object types for use in the table function
CREATE TYPE json_o AS OBJECT ( json_text VARCHAR2(30) );
/
CREATE TYPE json_t AS TABLE OF json_o;
/

-- and now for the table function
/*--------------------------------------------------------------------*/
CREATE OR REPLACE FUNCTION get_json_one ( p_curs SYS_REFCURSOR )
                  RETURN json_t
                  PIPELINED AS
/*--------------------------------------------------------------------*/
  v VARCHAR2(30);
BEGIN
  LOOP
    FETCH p_curs INTO v;
    EXIT WHEN p_curs%NOTFOUND;
    PIPE ROW(json_o(v));
  END LOOP;
END;
/

Then the function is called from a query like this:

SQL> SELECT JSON_VALUE(j.json_text,'$.fieldtwo') two
 2     FROM TABLE(get_json_one(CURSOR(SELECT jt.json_column.fieldone
 3                                      FROM json_table jt))) j
 4 /

TWO
--------------------
1 and 2
2 and 2
3 and 2

3 rows selected.

I’ve already covered table functions a lot in other posts so I’ll just explain the JSON portions of the code. First up is the REF Cursor. When run in SQL*Plus it looks like this:

SQL> SELECT jt.json_column.fieldone
 2 FROM json_table jt;

FIELDONE
------------------------------
{"fieldtwo":"1 and 2"}
{"fieldtwo":"2 and 2"}
{"fieldtwo":"3 and 2"}

3 rows selected.

It uses dot notation to get the value of fieldone from the json column in the table returning it as a JSON Object (squiggly brackets}. The JSON object is then passed to the JSON_VALUE function which extracts the value for fieldtwo.

JSON_VALUE(j.json_text,'$.fieldtwo')

You are probably thinking that is a lot of code to do such a simple thing and you are right. But its a nice way to change over from table functions to JSON SQL.

Here is a much simpler way to do the same thing as the table function.

SQL> SELECT JSON_VALUE(j.f1,'$.fieldtwo') f2
  2    FROM ( SELECT jt.json_column.fieldone AS f1
  3             FROM json_table jt ) j;0

F2
------------------------------
1 and 2
2 and 2
3 and 2

3 rows selected.

The next few posts will explore JSON SQL in more detail.

Thanks for reading!

Validate Parallel Pipelined Functions (Careful Outside Of Cursor Loop)

All of the previous examples in this set of posts focus on returning rows from a REF Cursor Loop.

This post explains what happens when rows are returned from outside the REF Cursor Loop. As always – lets dive right into the code!

-- a simple demo table
CREATE TABLE demo
( col1 NUMBER,
  col2 NUMBER );

-- object types for return values from the function
CREATE TYPE return_o AS OBJECT ( col1    NUMBER,         -- col1 value from REF Cursor
                                 pq      VARCHAR2(5),    -- PQ Server row process by
                                 ts      DATE,           -- timestamp row returned
                                 rowtype VARCHAR2(10) ); -- (In Loop OR After Loop)
/
CREATE TYPE return_t AS TABLE OF return_o;
/

-- a package to define the REF Cursor
CREATE OR REPLACE PACKAGE demo_curs AS
  TYPE v_demo_curs IS REF CURSOR RETURN demo%ROWTYPE;
END;
/

-- Some demo data
BEGIN
  FOR counter1 IN 1..2 LOOP
    FOR counter2 IN 1..2 LOOP
      INSERT INTO demo
      VALUES(counter1,counter2);
    END LOOP;
  END LOOP;
END;
/

-- And last but not least - the function
CREATE OR REPLACE FUNCTION demo_function ( p_curs demo_curs.v_demo_curs )
                           RETURN return_t
                           PARALLEL_ENABLE ( PARTITION p_curs BY RANGE(col1) )
                           ORDER p_curs BY ( col1 )
                           PIPELINED AS
  v_col1 NUMBER := 0;
  v_col2 NUMBER;
  v_pq VARCHAR2(5);
BEGIN
  BEGIN
    SELECT server_name
      INTO v_pq
      FROM v$px_process
     WHERE sid = ( SELECT sid
                     FROM v$mystat
                    WHERE rownum = 1 );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_pq := 'N/A';
  END;

  LOOP
    FETCH p_curs INTO v_col1, v_col2;
    EXIT WHEN p_curs%NOTFOUND;
    -- return row from inside cursor loop
    PIPE ROW(return_o(v_col1,v_pq,SYSDATE,'In Loop'));
  END LOOP;

  -- return row from outside cursor loop
  PIPE ROW(return_o(v_col1,v_pq,SYSDATE,'After Loop'));

  RETURN;

END;

Now to see what is returned in non-parallel mode.

SQL> BEGIN
  2    FOR x IN ( SELECT col1,
  3                      pq,
  4                      rowtype
  5                 FROM TABLE(demo_function(CURSOR(SELECT *
  6                                                   FROM demo))) ) LOOP
  7      DBMS_OUTPUT.PUT_LINE(x.col1 || '|' ||
  8                           x.pq   || '|' ||
  9                           x.rowtype);
 10    END LOOP;
 11  END;
 12  /
1|N/A|In Loop
1|N/A|In Loop
2|N/A|In Loop
2|N/A|In Loop
2|N/A|After Loop

That output is straightforward. Two In Loop rows for COL1=1, Two In Loop rows for (COL1=2) and one After Loop row with COL1=2. Note that the COL1 value of 2 was left over from the preceding cursor loop row.

Now for the parallel mode results:

0|P002|After Loop
2|P001|In Loop
2|P001|In Loop
2|P001|After Loop
1|P000|In Loop
1|P000|In Loop
1|P000|After Loop
0|P003|After Loop

Allow me to explain with 3 points:

  1. Rows 2 through 4 (shown in Green) came from PQ Server P001 which processed COL1=2 Рit included two In Loop rows and one After Loop row
  2. Rows 5 through 7 (shown in Blue) show that PQ Server P000 did the same for COL=1
  3. Rows 1 and 8 (shown in Red) came from PQ Servers (P002 and P003 respectively) that were used but did not return any rows from the Ref Cursor

Points 1 and 2 are logical because the code in the function was essentially run twice so two After Loop rows were created.

NB: There is no way to predict how many PQ servers will be used. For example added rows for COL1=3 would not guarantee a 3rd PQ Server would be used.

Point 3 requires some code changes to deal with. I’ll use a boolean to check if rows were returned by the Ref Cursor.

CREATE OR REPLACE FUNCTION demo_function ( p_curs demo_curs.v_demo_curs )
                  RETURN return_t
                  PARALLEL_ENABLE ( PARTITION p_curs BY RANGE(col1) )
                  ORDER p_curs BY ( col1 )
                  PIPELINED AS
  v_col1 NUMBER := 0;
  v_col2 NUMBER;
  v_pq VARCHAR2(5);
  v_rows_found BOOLEAN := FALSE; -- were rows returned from cursor
BEGIN
  BEGIN
    SELECT server_name
      INTO v_pq
      FROM v$px_process
     WHERE sid = ( SELECT sid
                     FROM v$mystat
                     WHERE rownum = 1 );
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      v_pq := 'N/A';
  END;

  LOOP
    FETCH p_curs INTO v_col1, v_col2;
    EXIT WHEN p_curs%NOTFOUND;
    v_rows_found := TRUE;
    PIPE ROW(return_o(v_col1,v_pq,SYSDATE,'In Loop'));
  END LOOP;

  -- if rows returned by cursor then return After Loop row
  IF v_rows_found THEN
    PIPE ROW(return_o(v_col1,v_pq,SYSDATE,'After Loop'));
  END IF;

  RETURN;
END;

And that produces much better results.

1|P000|In Loop
1|P000|In Loop
1|P000|After Loop
2|P001|In Loop
2|P001|In Loop
2|P001|After Loop

Remember these behaviors when returning rows from outside the cursor loop in parallel table functions.

Thanks for reading!