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

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:

16 thoughts on “Export Data into JSON File in Oracle 11g Using PL/SQL”

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

    1. 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. [Error] PLS-00201 (23: 42): PLS-00201: identifier ‘T_STR_ARRAY’ must be declared

      i got error like that when compiling package

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

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

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

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