📝 Working with JSON in PL/SQL: A Complete Guide for Oracle 12c and 19c

Native JSON support in Oracle 12c and 19c has brought a new dimension to PL/SQL development. You can now manipulate structured JSON data directly within the database — without relying on external tools or complex conversions.

In this guide, you'll learn:

• How to create, validate, and manipulate JSON in PL/SQL

• Key differences between Oracle 12c and 19c

• How to transform JSON into %ROWTYPE records

• Practical examples using JSON_OBJECT_T, JSON_TABLE, DBMS_JSON, and more

---

🧱 1. Creating JSON in PL/SQL (Oracle 19c)

Starting with Oracle 19c, native types like JSON_OBJECT_T and JSON_ARRAY_T allow you to build and manipulate JSON directly in PL/SQL.

DECLARE
   obj JSON_OBJECT_T := JSON_OBJECT_T();
BEGIN
   obj.put('name', 'Johnny');
   obj.put('age', 30);
   obj.put('city', 'Maringá');

   DBMS_OUTPUT.PUT_LINE(obj.stringify); -- {"name":"Johnny","age":30,"city":"Maringá"}
END;

✅ Advantages:

• Direct manipulation in PL/SQL

• Strongly typed and structured

• Ideal for API integrations and dynamic data handling

---

🔍 2. Reading and Accessing JSON Data

You can use methods like get_string, get_number, and get_array to extract values from JSON objects:

DECLARE
   v_json_text CLOB := '{"name":"Ana","age":25}';
   obj JSON_OBJECT_T;
BEGIN
   obj := JSON_OBJECT_T.parse(v_json_text);

   DBMS_OUTPUT.PUT_LINE('Name: ' || obj.get_string('name'));
   DBMS_OUTPUT.PUT_LINE('Age: ' || obj.get_number('age'));
END;

---

🛡️ 3. Validating JSON (Oracle 12c and 19c)

Both Oracle 12c and 19c support JSON validation using DBMS_JSON.IS_VALID:

DECLARE
   v_json CLOB := '{"product":"Laptop","price":3500}';
BEGIN
   IF DBMS_JSON.IS_VALID(v_json) THEN
      DBMS_OUTPUT.PUT_LINE('Valid JSON');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Invalid JSON');
   END IF;
END;

🔒 Validating JSON in table columns

CREATE TABLE orders (
   id NUMBER,
   order_data CLOB CHECK (order_data IS JSON)
);

---

📊 4. Extracting Data with JSON_TABLE (Oracle 12c+)

JSON_TABLE converts JSON into relational rows and columns — perfect for reporting, joins, and structured queries.

SELECT name, age
FROM JSON_TABLE(
   '{"clients":[{"name":"João","age":28},{"name":"Maria","age":32}]}',
   '$.clients[*]'
   COLUMNS (
      name VARCHAR2(50) PATH '$.name',
      age  NUMBER       PATH '$.age'
   )
);

---

🔄 5. Differences Between Oracle 12c and 19c

---

🚀 6. Practical Use Cases

• REST API integration using Oracle REST Data Services (ORDS)

• Flexible data storage without rigid schemas

• Dynamic reporting with JSON_TABLE

• Payload validation for web services and external systems

---

🧩 7. Working with Nested JSON and %ROWTYPE in PL/SQL

✅ Example: Mapping nested JSON to table records

Sample JSON:

{
  "clients": [
    {
      "id": 1,
      "name": "João",
      "email": "joao@email.com",
      "address": {
        "street": "Rua A",
        "city": "São Paulo",
        "zip": "01000-000"
      }
    },
    {
      "id": 2,
      "name": "Maria",
      "email": "maria@email.com",
      "address": {
        "street": "Rua B",
        "city": "Rio de Janeiro",
        "zip": "20000-000"
      }
    }
  ]
}

Tables:

CREATE TABLE clients (
   id NUMBER,
   name VARCHAR2(100),
   email VARCHAR2(100)
);

CREATE TABLE addresses (
   client_id NUMBER,
   street    VARCHAR2(100),
   city      VARCHAR2(100),
   zip       VARCHAR2(20)
);

PL/SQL Code:

DECLARE
   v_json CLOB := '...'; -- JSON as above

   CURSOR c_json IS
     SELECT *
     FROM JSON_TABLE(
       v_json,
       '$.clients[*]'
       COLUMNS (
         id     NUMBER        PATH '$.id',
         name   VARCHAR2(100) PATH '$.name',
         email  VARCHAR2(100) PATH '$.email',
         street VARCHAR2(100) PATH '$.address.street',
         city   VARCHAR2(100) PATH '$.address.city',
         zip    VARCHAR2(20)  PATH '$.address.zip'
       )
     );

   v_client   clients%ROWTYPE;
   v_address  addresses%ROWTYPE;

BEGIN
   FOR rec IN c_json LOOP
      v_client.id := rec.id;
      v_client.name := rec.name;
      v_client.email := rec.email;

      v_address.client_id := rec.id;
      v_address.street := rec.street;
      v_address.city := rec.city;
      v_address.zip := rec.zip;

      INSERT INTO clients VALUES v_client;
      INSERT INTO addresses VALUES v_address;
   END LOOP;
END;

---

🔁 Example: Processing JSON in memory with variables (no tables)

DECLARE
   v_json CLOB := '...'; -- same JSON structure

   TYPE t_client IS RECORD (
     id     NUMBER,
     name   VARCHAR2(100),
     email  VARCHAR2(100),
     street VARCHAR2(100),
     city   VARCHAR2(100),
     zip    VARCHAR2(20)
   );

   v_client t_client;

   CURSOR c_json IS
     SELECT *
     FROM JSON_TABLE(
       v_json,
       '$.clients[*]'
       COLUMNS (
         id     NUMBER        PATH '$.id',
         name   VARCHAR2(100) PATH '$.name',
         email  VARCHAR2(100) PATH '$.email',
         street VARCHAR2(100) PATH '$.address.street',
         city   VARCHAR2(100) PATH '$.address.city',
         zip    VARCHAR2(20)  PATH '$.address.zip'
       )
     );

BEGIN
   FOR rec IN c_json LOOP
      v_client.id := rec.id;
      v_client.name := rec.name;
      v_client.email := rec.email;
      v_client.street := rec.street;
      v_client.city := rec.city;
      v_client.zip := rec.zip;

      DBMS_OUTPUT.PUT_LINE(
         'Client: ' || v_client.name ||
         ' | City: ' || v_client.city ||
         ' | ZIP: ' || v_client.zip
      );
   END LOOP;
END;

---

📣 Final Thoughts

JSON support in Oracle has matured significantly between versions 12c and 19c. With tools like JSON_TABLE, DBMS_JSON, and native types like JSON_OBJECT_T, developers can work with structured data in PL/SQL safely, efficiently, and in a modern way.

If you work with REST APIs, ETL pipelines, or flexible data formats, mastering these tools is a powerful technical advantage. And even in older versions, libraries like PL/JSON offer robust solutions for JSON manipulation.

Want to learn more about PLJSON? You can find it here: https://johnnyhideki.hashnode.dev/working-with-json-in-oracle-using-the-pljson-library

#OraclePLSQL #JSONinOracle #PLSQLTips #Oracle12c #Oracle19c #JSON_TABLE #DBMS_JSON #BackendDevelopment #APIs #RESTIntegration #SQLDev #TechBlog #DatabaseEngineering #OracleTips