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

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:

Vinish Kapoor
Vinish Kapoor

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.

Related Posts

16 Comments

  1. 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))

  4. 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"
    },

    • 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. 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?

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

  7. 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.