Inserting into JSON datatype receive the error ORA-40441: JSON syntax error (Doc ID 3013265.1)

Last updated on JUNE 27, 2024

Applies to:

Symptoms

-- Please note, this is a new feature available in Oracle 23ai Databases.

-- Firstly create a table that has a JSON datatype column:

SQL> CREATE TABLE j_purchaseorder
(id VARCHAR2 (32) NOT NULL PRIMARY KEY,
po_document JSON);

Table created.

-- Confirm the column is a JSON datatype

SQL> desc j_purchaseorder
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(32)
PO_DOCUMENT JSON

-- insert well-formed JSON data

SQL> insert into j_purchaseorder values(1, '["a", "b"]');

1 row created.

-- attempt to insert JSON data that is not well-formed

SQL> insert into j_purchaseorder values(2, '["a" "b"]');
insert into j_purchaseorder values(2, '["a" "b"]')
*
ERROR at line 1:
ORA-40441: JSON syntax error

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.