Export Data into JSON File in Oracle 11g Using PL/SQL

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 3 mins read

In this article, I am giving an example to export data into JSON file in Oracle 11g using PL/SQL.

Follow These Steps to Export Data into JSON File in Oracle 11g

  1. First, download the Alexandria PL/SQL utility package from GITHUB using the following link: Download alexandria_plsql_utility package.
  2. After downloading the zip file, extract it and find the JSON_UTIL_PKG in the "alexandria-plsql-utils-master\alexandria-plsql-utils-master\ora" directory.
  3. Install JSON_UTIL_PKG package specification and body into your schema
  4. After that download the "WRITE_CLOB_TO_FILE" PL/SQL procedure from the following link: Download Write_Clob_To_File.
  5. Install this procedure also into your schema.

Now you can generate the JSON file from Oracle table using PL/SQL, as shown in below example.

Oracle 11g SQL to JSON Example

In the following example, we will get the data into CLOB variable using JSON_UTIL_PKG.SQL_TO_JSON function and then we will write that CLOB has JSON data into a FILE using WRITE_CLOB_TO_FILE procedure. We will pass three parameters to WRITE_CLOB_TO_FILE procedure, and they are as follows: a file name, directory object name, and the CLOB variable.

DECLARE
c CLOB;
BEGIN
SELECT json_util_pkg.sql_to_json('select EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO from emp2')
INTO c
FROM DUAL;

WRITE_CLOB_TO_FILE ('emp.json', 'JSON_DIR', c);
END;
/

Now you can check the location of directory object JSON_DIR, and you will find the JSON file as shown in below example output:

{"ROWSET":[{"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","MGR":7902,"HIREDATE":"17-DEC-80","SAL":800,"COMM":null,"DEPTNO":21},{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"20-FEB-81","SAL":1600,"COMM":300,"DEPTNO":30},{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","MGR":7698,"HIREDATE":"22-FEB-81","SAL":1250,"COMM":500,"DEPTNO":30},{"EMPNO":7566,"ENAME":"JONES","JOB":"MANAGER","MGR":7839,"HIREDATE":"04-FEB-81","SAL":2975,"COMM":null,"DEPTNO":20},{"EMPNO":7654,"ENAME":"MARTIN","JOB":"SALESMAN","MGR":7698,"HIREDATE":"28-SEP-81","SAL":1250,"COMM":1400,"DEPTNO":30},{"EMPNO":7698,"ENAME":"BLAKE","JOB":"MANAGER","MGR":7839,"HIREDATE":"05-JAN-81","SAL":2850,"COMM":null,"DEPTNO":30},{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","MGR":7839,"HIREDATE":"06-SEP-81","SAL":2450,"COMM":null,"DEPTNO":10},{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","MGR":7566,"HIREDATE":"19-APR-87","SAL":3000,"COMM":null,"DEPTNO":20},{"EMPNO":7839,"ENAME":"KING","JOB":"PRESIDENT","MGR":null,"HIREDATE":"17-NOV-81","SAL":5000,"COMM":null,"DEPTNO":10},{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","MGR":7698,"HIREDATE":"09-AUG-81","SAL":1500,"COMM":0,"DEPTNO":30},{"EMPNO":7876,"ENAME":"ADAMS","JOB":"CLERK","MGR":7788,"HIREDATE":"23-MAY-87","SAL":1100,"COMM":null,"DEPTNO":20},{"EMPNO":7900,"ENAME":"JAMES","JOB":"CLERK","MGR":7698,"HIREDATE":"12-MAR-81","SAL":950,"COMM":null,"DEPTNO":30},{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","MGR":7566,"HIREDATE":"12-MAR-81","SAL":3000,"COMM":null,"DEPTNO":20},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"23-JAN-82","SAL":1300,"COMM":null,"DEPTNO":10}]}

See also:

This Post Has 16 Comments

  1. Max September 18, 2020

    Good Day!

    This method work good. Ok))

    But we need result JSON file without top section {"ROWSET":......}, only JSON result

    [{"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","MGR":7902,"HIREDATE":"17-DEC-80","SAL":800,"COMM":null,"DEPTNO":21},
.......................
{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","MGR":7782,"HIREDATE":"23-JAN-82","SAL":1300,"COMM":null,"DEPTNO":10}]

    How can we reach this result?

    Thank you for future answer))

    1. Vinish Kapoor September 18, 2020

      You will have to use dbsm_lob.substr() to pick a particular part of the clob.

  2. kucridd September 22, 2020

    [Error] PLS-00201 (23: 42): PLS-00201: identifier 'T_STR_ARRAY' must be declared

    i got error like that

    1. Vinish Kapoor September 22, 2020

      Seems like, your package json_util_pkg not properly installed.

      Please download it from GitHub (mentioned in the first step) and read the readme.md file for instructions.

    2. kucridd September 24, 2020

      Error appear when installing (compile) JSON_UTIL_PKG package

    3. Vinish Kapoor September 24, 2020

      What error appeared?

    4. kucridd September 24, 2020

      [Error] PLS-00201 (23: 42): PLS-00201: identifier ‘T_STR_ARRAY’ must be declared

      i got error like that when compiling package

    6. Joan Marin October 20, 2020

      You must to run the types.sql file into this path: alexandria-plsql-utils/setup

  3. Jaydeepsinh November 9, 2020

    How we can add Root node in Json ouput ?

  4. Jaydeepsinh November 9, 2020

    Any way I can add Root Node ?

    Current Output : 
    {
    "EcmGstin":null,
    "IgstOnIntra":"N",
    "RegRev":"N",
    "SupTyp":"EXPQP"
    },

    Expected Output : 

    "TranDtls":
    {
    "EcmGstin":null,
    "IgstOnIntra":"N",
    "RegRev":"N",
    "SupTyp":"EXPQP"
    },

  5. Lamine BENCHIKH January 27, 2021

    Hello i'm new to Apex , Would you please help me how to do the step 3 and 5

    Thank you

    1. Vinish Kapoor January 28, 2021

      In step 3, you will run the package script in your schema which you have downloaded in step 1.

      In step 5, you will run the procedure script in your schema which you have downloaded in step 4.

  6. Toy February 1, 2021

    I have an error when I try to compile the PGK, I am using toad and it asks for values of variables, this is for "&", any Ide how to fix this?

    1. Vinish Kapoor February 1, 2021

      before running the script run the SET DEFINE OFF command. Then compile your database object.

  7. Ritesh Agrawal April 15, 2021

    This script doesn't appear to display square brackets for array of data.For E.g. for each deptartment, employee records are to be displayed in an array format. But those employee records for particular department should be in an array enclosed in "[" "]" brackets as json uses these square brackets to denote array. Could you clarify on this ?

Comments are closed.