PLJSON – JSON extension for PL/SQL

PLJSON

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

JSON

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

Example :-

1)

{“Oracle Corporation”:”oracle”}

2)

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

{

“emp”:[

                 {“empno”:”10″,”empname”:”clerk”},

                 {“empno”:”11″,”empname”:”bill”},

                 {“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 http://sourceforge.net/projects/pljson/

Facts about PL/JSON

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

2. License – Free license under MIT

3. Download – https://github.com/pljson/pljson

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);
begin
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
begin
dbms_sql.define_column(v_cursor,i,v_column,2000);
n_colcnt := i;
exception
when others then
if ( sqlcode = -1007) then — variable not in select
exit;
else
raise;
end if;
end;
end loop;

n_status := dbms_sql.execute(v_cursor);
dbms_sql.describe_columns(v_cursor,n_colcnt,v_coldesc);
loop
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;
dbms_sql.close_cursor(v_cursor);

— 382828
v_comma := null;
for k in tt.first..tt.last
loop
–dbms_output.put_line(tt(k));
v_mystring := v_mystring || v_comma || tt(k) ;
v_comma := ‘,’;
if length(v_mystring) > 28000 then
–dbms_output.put_line(length(v_mystring));
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;

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

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

 

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

3 thoughts on “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 ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s