2

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?

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
Kinjan Bhavsar
  • 1,439
  • 28
  • 58

2 Answers2

4

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

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • Thanks, I thought of the same but was checking whether I could do without calling any function? Also do you have any suggestion for my other post? https://stackoverflow.com/questions/54644055/how-to-create-manual-json-in-oracle-18c – Kinjan Bhavsar Apr 04 '19 at 14:47
  • @KinjanBhavsar : I'm sorry.I don't think I can ever answer Oracle JSON related question (or any SQL for that matter) which Chris Saxon couldn't (and he didn't answer it yet after you adding details in the question ). You may ask a new question linking the previous one with the required detail and may be he or somebody else could help you. Regarding this particular question, its upto you whether you want to wait for other answers ( may be Chris himself can, without using `TRIM`) or go ahead with this. – Kaushik Nayak Apr 04 '19 at 14:54
  • I used the workaround provided by you and its working fine. Thanks Also, if you can suggest anything for https://stackoverflow.com/questions/55535454/how-to-parse-json-efficiently-in-oracle-18c, it would be a great help. – Kinjan Bhavsar Apr 05 '19 at 12:35
1

In 19c, using put(..., '') you get an empty string. Using put_null, or put(..., to_char(null)), put(..., <unitializedString>) you get the "real" null value.

DECLARE
   V_OBJ JSON_OBJECT_T;
   V_STRING VARCHAR2(20);
BEGIN
   V_OBJ := JSON_OBJECT_T();
   V_OBJ.PUT('doubleSingleQuotes','');
   V_OBJ.PUT('toCharNull',to_char(null));
   V_OBJ.PUT('uninitializedStringVariable',v_string);
   V_OBJ.PUT_null('null');
   V_OBJ.PUT('trimSpace', trim(' '));
   DBMS_OUTPUT.PUT_LINE(V_OBJ.stringify);
END;

... gives :

{"doubleSingleQuotes":"","toCharNull":null,"uninitializedStringVariable":null,"null":null,"trimSpace":""}
FCA69
  • 185
  • 1
  • 5