jsonoracleplsqloracle12coracle18c

How to put empty string in JSON_OBJECT_T object?

I am trying to add an empty string to a JSON_OBJECT_T using the following code but I am getting null in value and not empty string.

DECLARE
   V_OBJ JSON_OBJECT_T;
BEGIN
   V_OBJ := JSON_OBJECT_T();
   V_OBJ.PUT('customerAccRef','');
   DBMS_OUTPUT.PUT_LINE(V_OBJ.stringify);
END;

When I do this, I am getting the following json

{"customerAccRef":null}

and I want output as below

{"customerAccRef":""}

Can someone suggest what change I need to do to pass an empty string?

Solution

  • The reason it is happening is due to the fact that Oracle internally changes empty string to NULL values. It is due to some legacy reason and you may read this answer to know the history.

    I couldn't find anywhere in the JSON documentation with an option to bypass this particular problem myself, although I'd be glad if someone could find it.

    As a workaround to your problem, you could use TRIM function to convert a single space to blank string.

    V_OBJ.PUT('customerAccRef' , TRIM(' '));

    which gives

    {"customerAccRef":""}

    This seems to work both in Oracle 12.2 version; I tested in my local machine and in Oracle 18c : DEMO, as well as in 19c (LiveSQL online)

    A point to also note here is that a simple select TRIM(' ') from dual always returns NULL, which is surprising and luckily for you, it works as expected with JSONs