Validating JSON in the database

April 24, 2017 at 6:04 pm | Posted in Oracle Developement | 3 Comments

Creating REST services and consuming JSON inside the Oracle database has become quite easy. The APEX_JSON package provides a number of useful procedures and functions to work with JSON. However, one thing I haven’t found is a way to validate JSON before parsing it.

To my knowledge the is not yet a PL/SQL way to check whether a JSON string is valid, i.e. to make sure that all the brackets, commas, colons, etc. are there. There is however, a column check constraint you can use in 12c that will only allow valid JSON to be inserted into a table. So here is a quick workaround for validating JSON using the check constraint: I simply take the JSON and attempt to insert it into the table. If the check constraint is violated, I’ll get an error.

I wrapped to whole thing in a function that will then simply return a boolean value for the JSON passed into it.

create table json_validator ( 
	 id   number generated always as identity primary key
	,json clob
	,constraint validate_json check (json is json format json)
);

create or replace function validate_json (l_json in clob)
return boolean
is
  l_id pls_integer;
  e_invalid_json exception;
  pragma exception_init (e_invalid_json,-2290);
  pragma autonomous_transaction;
begin
	insert into json_validator (json) values (l_json);
	rollback;
	return true;
exception
	when e_invalid_json then
	  return false;
end;
/

set serveroutput on
declare
  l_json  clob;
  l_return boolean;
begin
  l_json := '
				{"widget": {
					"debug": "on",
					"window": {
						"title": "sample konfabulator widget",
						"name": "main_window",
						"width": 500,
						"height": 500
					},
					"image": { 
						"src": "images/sun.png",
						"name": "sun1",
						"hoffset": 250,
						"voffset": 250,
						"alignment": "center"
					},
					"text": {
						"data": "click here",
						"size": 36,
						"style": "bold",
						"name": "text1",
						"hoffset": 250,
						"voffset": 100,
						"alignment": "center",
						"onmouseup": "sun1.opacity = (sun1.opacity / 100) * 90;"
					}
				}}    
  ';

  l_return := validate_json(l_json => l_json );

  if l_return then 
	dbms_output.put_line('JSON is valid');
  else
	dbms_output.put_line('JSON is NOT valid');
  end if;

end;
/

Advertisements

3 Comments »

RSS feed for comments on this post. TrackBack URI

  1. Couple ideas; You could APEX_JSON.PARSE
    Or inline sub select the JSON to avoid the validator table

  2. will this code works in11g 4 db??


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


Entries and comments feeds.

%d bloggers like this: