🧩 Working with JSON in Oracle Using the PL/JSON Library

📌 Introduction

With the rise of REST APIs and data exchange in JSON format, PL/SQL developers face the challenge of handling JSON in Oracle environments that lack native support. While Oracle 19c and newer versions include types like JSON_OBJECT_T, many systems still run on older versions such as 11g or 12c.

That’s where PL/JSON comes in — an open-source library written entirely in PL/SQL that allows you to create, manipulate, and navigate JSON objects easily and efficiently.

---

📦 What is PL/JSON?

PL/JSON is a library that simulates JSON objects in PL/SQL. It offers an object-oriented API to:

• Create JSON objects and arrays

• Serialize and deserialize JSON

• Validate JSON structures

• Navigate through paths and keys

🔗 Official repository: https://github.com/pljson/pljson

---

⚙️ Installation

Requirements

• Oracle Database (11g, 12c or higher)

• Permission to execute PL/SQL scripts

• Access to SQL*Plus, SQL Developer, or similar tools

Steps

1. Clone the repository:

git clone https://github.com/pljson/pljson

2. Navigate to the pljson folder and run the installation scripts:

@pljson_install.sql

3. Verify the installation:

SELECT object_name FROM user_objects WHERE object_type = 'PACKAGE' AND object_name LIKE 'PLJSON%';

---

🧪 Practical Examples

1. Creating a JSON object

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

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

---

2. Working with JSON arrays

DECLARE
   arr pljson_list := pljson_list();
BEGIN
   arr.append(pljson_value('banana'));
   arr.append(pljson_value('apple'));
   arr.append(pljson_value('orange'));

   DBMS_OUTPUT.PUT_LINE(arr.to_char);
   -- Output: ["banana","apple","orange"]
END;

---

3. Accessing values by key

DECLARE
   obj pljson := pljson();
   name VARCHAR2(100);
BEGIN
   obj.put('name', 'Johnny');
   name := obj.get('name').get_string;

   DBMS_OUTPUT.PUT_LINE('Name: ' || name);
END;

---

4. Validating JSON

DECLARE
   json_text VARCHAR2(4000) := '{"product":"Laptop","price":3500}';
   obj pljson;
BEGIN
   obj := pljson_parser.parse(json_text);

   IF obj.is_valid THEN
      DBMS_OUTPUT.PUT_LINE('Valid JSON!');
   ELSE
      DBMS_OUTPUT.PUT_LINE('Invalid JSON!');
   END IF;
END;

---

🆚 Comparison: PL/JSON vs Oracle 19c Native JSON Support

---

📚 Conclusion

The PL/JSON library is a powerful and flexible solution for developers working with JSON in Oracle versions that lack native support. With an intuitive API and an active community, it enables PL/SQL developers to integrate modern JSON-based systems with ease.

If you're working on Oracle 11g or 12c, PL/JSON is a must-have tool. Even in newer versions, it can be useful for specific scenarios or as a lightweight alternative

#OraclePLSQL #JSONinOracle #PLJSON #OracleDatabase #SQLTips #DatabaseDevelopment #Oracle12c #Oracle19c #APIs #RESTIntegration #OpenSourceTools #TechBlog #DevTips #BackendDevelopment