JSON is the the dominate vehicle for allowing for easy exchange of structured information between different programming languages and platforms – or for interacting with REST APIs.
When we deal with the contents of an Oracle Database TABLE via REST, we use JSON as the data exchange format.
I can POST in a JSON object to have it loaded as a row in my table, and I can GET it back out as another JSON object. Or if we’re talking about multiple rows, then we would have an array of said objects.
I’ve shown how this works differently for GETs in 19 and 23 databases, you can read that post, here.
I am learning here too, sometimes
Talking about REST APIs and JSON and INSERTs, isn’t something hew here. I’ve shown how to add JSON docs amongst relational data in the request before. But, I made it much harder than it needed to be, by splitting up the BODY for just a single JSON body and the relational attributes coming in as REQUEST headers.
That approach works, but it’s not as clean. And, if you have MULTIPLE JSON columns to populate, then, it doesn’t work. So, while you might think I’m an ‘expert,’ I’m the kind of expert that still has plenty to learn and improve upon.
Reading my blog means you get to witness my learning path, so keep that in mind when you feel bad or down on yourself because you’re just not getting it.
Example: 19c Relational Table, column having IS_JSON constraint
The Code
CREATE TABLE PENDING_CASES
( REQUEST_ID VARCHAR2(256 BYTE) ,
EMAIL VARCHAR2(256 BYTE) ,
PRINCIPAL VARCHAR2(256 BYTE) ,
USAGE_TYPE VARCHAR2(256 BYTE) ,
TEST_CASE_NAME VARCHAR2(256 BYTE) ,
RAW_REQUEST VARCHAR2(32000 BYTE) , -- this will have JSON data
PROGRAMMING_LANGUAGE VARCHAR2(256 BYTE)
) DEFAULT COLLATION USING_NLS_COMP ;
CREATE UNIQUE INDEX PENDING_CASES_PK ON PENDING_CASES (REQUEST_ID)
;
ALTER TABLE PENDING_CASES ADD CONSTRAINT ENSURE_JSON CHECK (raw_request is json) ENABLE;
ALTER TABLE PENDING_CASES ADD CONSTRAINT PENDING_CASES_PK PRIMARY KEY (REQUEST_ID)
USING INDEX ENABLE;So let’s look at my POST handler for adding a row to this table.
BEGIN
INSERT INTO PENDING_cases (
request_id,
email,
principal,
usage_type,
test_case_name,
raw_request,
programming_language
) VALUES (
:opc-request-id,
:email,
:principal,
:usage-type,
:test-case-name,
JSON_SERIALIZE(:raw-request),
:programming-language
);
COMMIT;
:status_code := 201;
:forward_location := :opc-request-id;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
:error_message := 'Error: Duplicate primary key value';
:status_code := 400;
WHEN OTHERS THEN
:error_message := 'An error occurred: ' || SQLERRM;
:status_code := 400;
END;My request body will look like this –
curl --request POST \
--url https://the-cloud.somewhere.com/ords/admin/requests/pending/ \
--header 'Content-Type: application/json' \
--header 'User-Agent: insomnia/10.3.0' \
--data '{
"opc-request-id": "wow4",
"email": "[email protected]",
"principal": "some made up stuff",
"usage-type": "UNITTEST",
"test-case-name": "bingbong",
"raw-request": "[{\n \"id\": 28,\n \"Title\": \"Sweden\"\n}, {\n \"id\": 56,\n \"Title\": \"USA\"\n}, {\n \"id\": 89,\n \"Title\": \"England\"\n}]",
"programming-language": "java"
}'So I’m going to POST a JSON document, that has both the relational columns, and also the VARCHAR2 which is pseudo-typed as JSON via a CHECK constraint.
I need to make the JSON content for the “raw-request’ attribute to be http/url encoded, hence the \ escape characters, easy with this online tool.
My INSERT is simply, I merely use our ORDS feature that allows to you directly work with the attributes on the incoming JSON payload by referencing them as :bind variables.
For inserting the JSON content, I’m going to use the JSON_SERIALIZE function. ORDS will treat this data as a VARCHAR, it has no notion of a JSON type in the 19c database, because it doesn’t exist, yet.
The Request
When I do my POST, I can see the response seems to be good, and I can double-confirm this by querying the row out of the table.
It gets even easier when we have a native JSON type that the database and JDBC drivers can navigate.
Example: 23ai Relational Table, column having IS_JSON constraint
The Code
Our TABLE DDL is simpler, no more CHECK constraint –
CREATE TABLE PENDING_CASES
( REQUEST_ID VARCHAR2(256 BYTE),
EMAIL VARCHAR2(256 BYTE),
PRINCIPAL VARCHAR2(256 BYTE),
USAGE_TYPE VARCHAR2(256 BYTE),
TEST_CASE_NAME VARCHAR2(256 BYTE),
RAW_REQUEST JSON,
PROGRAMMING_LANGUAGE VARCHAR2(256 BYTE)
) ;
CREATE UNIQUE INDEX PENDING_CASES_PK ON PENDING_CASES (REQUEST_ID)
;
ALTER TABLE PENDING_CASES ADD CONSTRAINT PENDING_CASES_PK PRIMARY KEY (REQUEST_ID)
USING INDEX ENABLE;And our REST POST HANDLER is also simpler, no more JSON_SERIALIZE function call over the :column bind for the INSERT.
BEGIN
INSERT INTO PENDING_cases (
request_id,
email,
principal,
usage_type,
test_case_name,
raw_request,
programming_language
) VALUES (
:opc-request-id,
:email,
:principal,
:usage-type,
:test-case-name,
:raw-request,
:programming-language
);
COMMIT;
:status_code := 201;
:forward_location := :opc-request-id;
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
:error_message := 'Error: Duplicate primary key value';
:status_code := 400;
WHEN OTHERS THEN
:error_message := 'An error occurred: ' || SQLERRM;
:status_code := 400;
END;The curl is exactly the same, I only change up the URL of the request because I’m using my local 23ai FREE virtualbox rig vs my Always Free 19c Autonomous Database service.
The Request
Summary
Always keep asking questions, always be learning, and things are often easier than they might appear on the surface.
2 Comments
Somewhat tangential to this article – could you use ORDS to load data from SQL Server into Oracle DB?
From anywhere that has a http client built in and that includes SQL Server
https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-invoke-external-rest-endpoint-transact-sql?view=fabric&tabs=request-headers