PLJSON – JSON extension for PL/SQL


PL/JSON is a generic JSON object written in PL/SQL.


JSON (JavaScript Object Notation) ( ) is a lightweight data-interchange format. It is a text based lightweight xml object.

Example :-


{“Oracle Corporation”:”oracle”}


Consider emp table with 2 columns and 3 records

Empno    Empname
10            clerk
11            bill
12            chang

Above records can be written in JSON format as





                 {“empno”:”12″,”empname”:”chang”} ]


In PL/SQL we can convert table data into JSON using PL/JSON generic JSON object. It is an excellent third party library available in

Facts about PL/JSON

1. Written by – Jonas Krogsboell ( inspired by code from Lewis R Cunningham )

2. License – Free license under MIT

3. Download –

4. Installation – Download zip file and and extract. Run install.sql ( You should have required privileges to run )

PLJSON is one of the excellent library written in PL/SQL. I have seen many people using it and excellent reviews.

It has lot of advantages

This sparkled me to write an standalone PL/SQL API to convert Oracle SQL to JSON format without creating any dependent object or types. My intention was to develop an simple API. This API takes sql as arguments and converts to JSON and clob format.The client program call the API and manipulate the JSON and process it. It is not using any ref cursor object here.

REF Cursors are cool. They allow you to encapsulate SQL queries behind a PL/SQL package API. For example, you can create a function called GET_EMPLOYEES that returns a SYS_REFCURSOR containing the employees in a specific department:

I assume this API is not for very large conversion projects. But relatively small systems. API Code given below


create or replace function sql_json_clob(p_sql varchar2) return clob
is– Author : Manoj
— Date : 3-Mar-2016
— Purpose: Converts sql to json format

v_json_clob clob := empty_clob();
v_cursor integer := dbms_sql.open_cursor;
v_coldesc dbms_sql.desc_tab;
v_seperator char(1);
v_comma char(1);
v_column varchar2(500);
v_columnvalue varchar2(4000);
v_columnname varchar2(4000);
n_colcnt pls_integer := 0;
n_cnt pls_integer := 0;
n_status integer;
v_sql varchar2(32767);
v_rowstring varchar2(32767);
v_title varchar2(50) := ‘dept’;
Type typjson is table of varchar2(32767) index by pls_integer;
n pls_integer := 0;
tt typjson;
v_mystring varchar2(32767);
v_sql := p_sql;
— to find the column count
dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);
for i in 1..255 loop
n_colcnt := i;
when others then
if ( sqlcode = -1007) then — variable not in select
end if;
end loop;

n_status := dbms_sql.execute(v_cursor);
exit when ( dbms_sql.fetch_rows(v_cursor) <= 0);
v_rowstring := null;
v_seperator := null;
for i in 1..n_colcnt loop
dbms_sql.column_value ( v_cursor, i, v_columnvalue);
v_columnname := v_coldesc(i).col_name;
v_rowstring := v_rowstring || v_seperator || ‘”‘ || v_columnname || ‘”: “‘ || v_columnvalue || ‘”‘ ;
v_seperator := ‘,’;
end loop;
v_rowstring := ‘{‘ || v_rowstring || ‘}’;
–v_json_clob := v_json_clob || v_comma || v_rowstring;
v_comma := ‘,’;
n := n + 1;
tt(n) := v_rowstring;
end loop;

— 382828
v_comma := null;
for k in
v_mystring := v_mystring || v_comma || tt(k) ;
v_comma := ‘,’;
if length(v_mystring) > 28000 then
v_json_clob := v_json_clob || v_mystring ;
v_mystring := null;
end if;
end loop;
if v_mystring is not null then
v_json_clob := v_json_clob || v_mystring;
end if;

v_json_clob := ‘{“Success”: “true”,”‘ || v_title || ‘”:[‘ || v_json_clob || ‘]}’;
return v_json_clob;

when others then
v_json_clob := ‘{“Success”: “true”,”‘ || v_title || ‘”:[]}’;
if dbms_sql.is_open(v_cursor) then
end if;
return v_json_clob;
end sql_json_clob;

SQL> select sql_json_clob(‘select id,id as name from table where rownum < 10000’) from dual;


It might need liitlebit testing and modification.

I appreciate your comments and feedback.


5 responses to “PLJSON – JSON extension for PL/SQL”

  1. Nice One Manoj! Looks Really Light weight, Just one question, can we change the varchar2 delcared as output and send it out as a temporary table clob column ?

  2. Hi Manoj, CAn you please post script on how to read a JSON file to a pl/sql table in oracle 11g?

  3. What version of oracle it works? because it donsn’t work en oracle 11g, please help!

    1. Oracle 11g also it will work.

  4. Oracle 11g also it will work.