Question today: why are you showing null vs ” when we GET data from our table?internal developer
Let’s take a quick look at what they’re after. We’ll create a TABLE, put some data in it (or lack of data), and then do a SELECT * FROM style REST API.
CREATE TABLE NULLS ( ID INTEGER, MESSAGE VARCHAR2(25) ); INSERT INTO NULLS VALUES ( 1, 'hello' ); INSERT INTO NULLS VALUES ( NULL, 'hello null' ); INSERT INTO NULLS VALUES ( 2, NULL ); INSERT INTO NULLS VALUES ( 3, '' );
Our REST API
When we call the API from our browser…
Why doesn’t ORDS show ” for a null?
The answer is simply because the database returns a null, not an empty string (Docs).
SQLcl & SQLPlus
What are my options?
If you insist on doing something else, you could…
Do something confusing/bad like this:
For numbers folks will often replace NULLs with Zero (0), but only you can decide if that’s appropriate or dangerous based on your data model and application logic.
Write some code
To actually return an empty string, ” vs null, you’d have to construct the JSON payload yourself and return that. I don’t recommend that.
And since I don’t recommend it, I’m not going to show you how to do that. But I do have a post on generating your own responses.
Otherwise you could put code ‘somewhere in the middle’ to transform the payloads from the REST APIs to re-write the nulls to ”, or…yeah you’re going to write some code, somewhere.