Austin Tatious Escapes His Characters

Using PLSQL Index By Table’s (IBT’s) is something Austin has been doing for a million years. HIs algorithm to loop through the IBT and add values to the JSON Array originally was this:

DECLARE
  v VARCHAR2(30);
BEGIN
  -- loop through the sparse IBT values and
  -- load them into the JSON Array
  v := v_unique_sites.FIRST;
  LOOP
    EXIT WHEN v IS NULL;
    v_array2.APPEND(v);
    v := v_unique_sites.NEXT(v);
  END LOOP;
END;

That produced output (Yaayyy!) but with backslashes (Booo!).

NM         SITES
---------- --------------------------------------------------
Cooter     ["\"Hospital\""]
Roscoe     ["\"Clinic\"","\"Hospital\"","\"Lab\""]
Roscoe     ["\"Lab\""]
Enos       ["\"Clinic\"","\"Hospital\"","\"Lab\""]
Daisy      ["\"Outpatient\""]

Reading the Oracle documentation explains the backslashes are escape characters. Austin sets a reminder to research this further but for now he just wants to get past the problem. He reasons there is an implicit conversion to JSON in the array APPEND call so he sets about making the conversion explicit with this code.

-- create a JSON Object named WTF with the site name as its value
-- for example {"WTF":"Hospital"}
v_jo := JSON_OBJECT_T.PARSE('{"WTF":' || v || '}');
v_array2.APPEND(v_jo.get('WTF'));

That produces the desired (no backslashes) result.

NM         SITES
---------- --------------------------------------------------
Cooter     ["Hospital"]
Roscoe     ["Clinic","Hospital","Lab"]
Roscoe     ["Lab"]
Enos       ["Clinic","Hospital","Lab"]
Daisy      ["Outpatient"]

In the next post in this series Austin digs deeper into what escape characters are and why they are required.

Thanks for reading!

Leave a Comment