A REST Enabled SQL View!

I put together a small Oracle PL/SQL package which demonstrates how to access REST Enabled SQL service and standard REST end points through PL/SQL and SQL.

It can also dynamically create an Oracle Database View to access information from a REST Enabled SQL request. This view can be referenced in SQL just like any other view but the data is provided from the REST Enabled SQL service which could be running against another database.

Maybe an alternative to Oracle database links?

Examples using RESTSQL package

	SET LONG 4000
	SET pagesize 1000
	var v_response CLOB;
	-- show the json response from a rest enabled sql request
	EXEC :v_response:= restsql.request('http://10.175.200.185:8087/ords/demo/_/sql','DEMO','demo','select 2 as mycol from dual');
	PRINT :v_response;
	--show the json response from a standard rest service
	--the example gets the response from a rest enabled view
	EXEC :v_response := restsql.request('http://10.175.200.185:8087/ords/demo/demo_view/');
	PRINT :v_response;
	--show the sql query that could be used to access the rest enabled sql service
	EXEC :v_response := restsql.rest_query('http://10.175.200.185:8087/ords/demo/_/sql','DEMO','demo','select 2 as mycol from dual');
	PRINT :v_response;
	--create a view called MY_NEW_VIEW which uses the rest enabled sql service to provide its data
	EXEC restsql.rest_view('MY_NEW_VIEW','http://10.175.200.185:8087/ords/demo/_/sql','DEMO','demo','select 2 as mycol from dual');
	--show the new view definition. note the JSON_TABLE definition which was generated
	SELECT DBMS_METADATA.GET_DDL('VIEW','MY_NEW_VIEW') FROM DUAL;
	--use the view in standard sql statements
	--note that the view performs a http request to the rest enabled sql service each time
	SELECT * FROM MY_NEW_VIEW;

view raw restsql_examples.sql hosted with ❤ by GitHub

restsql.pks

	create or replace PACKAGE "RESTSQL"
	AS
	/**
	* Project: RESTSQL utilities
	* Description: Package which works with ORDS to provide SQL access to REST services
	* Note: SQL*Plus examples print out CLOB. Best to use the following so the CLOB values are displayed correctly in SQL*Plus
	* set long 4000
	* set pagesize 1000
	* @author: dermot.oneill@oracle.com
	*/
	/**
	*JSON path used by REST Enabled SQL to provide result set items
	*/
	resultPath VARCHAR2(100) := '$.items.resultSet.items[*]';
	/**
	* Performs a http request to the rest enabled sql service and returns the result as a clob
	*
	* SQL*Plus Ex:
	* var v_response CLOB;
	* exec :v_response := restsql.request('https://localhost:8087/ords/demouser/_/sql','DEMO','demopassword',q'!select 'Hello World' as myCol from dual!');
	* print :v_response;
	*
	* @param p_url The REST Enabled SQL url. Ex: https://localhost:8087/ords/demouser/_/sql
	* @param p_username The username to authenticate REST Enabled SQL service. Ex: DEMO
	* @param p_password The password to authenticate REST Enabled SQL service. Ex: demoPassword
	* @param p_sql The sql statement that is to be run using REST Enabled SQL service. Ex: select 'Hello World' as myCol from dual
	* @returns CLOB The JSON response from the rest enabled sql service
	**/
	FUNCTION request(
	p_url VARCHAR2,
	p_username VARCHAR2,
	p_password VARCHAR2,
	p_sql CLOB)
	RETURN CLOB;
	/**
	* Performs a http request to the rest service and returns the result as a clob
	*
	* SQL*Plus Ex:
	* var v_response CLOB;
	* exec :v_response := restsql.request('https://localhost:8087/ords/demouser/myview/');
	* print :v_response;
	*
	* @param p_url The REST url. Ex: https://localhost:8087/ords/demouser/myview/
	* @param p_username The username to authenticate REST service. Ex: DEMO
	* @param p_password The password to authenticate REST service. Ex: demoPassword
	* @returns CLOB The JSON response from the rest service
	**/
	FUNCTION request(
	p_url VARCHAR2,
	p_username VARCHAR2 DEFAULT NULL,
	p_password VARCHAR2 DEFAULT NULL)
	RETURN CLOB;
	/**
	* Performs a http request to the rest enabled sql service and using the metadata in the response, generates a sql query which can be used to access the result of the rest enabled sql service in the future
	* The sql query is returned as a clob, which can then be saved, used to "create view" or executed immediately
	* The sql query returned uses Oracles JSON_TABLE syntax to represent the JSON response from rest enabled sql as a table.
	* This is useful when you want to combine the response of the rest enabled sql service within a SQL statement.
	*
	* SQL*Plus Ex:
	* var v_response CLOB;
	* exec :v_response := restsql.request_query('https://localhost:8087/ords/demouser/_/sql','DEMO','demopassword',q'!select 'Hello World' as myCol from dual!');
	* print :v_response;
	*
	* @param p_url The REST Enabled SQL url. Ex: https://localhost:8087/ords/demouser/_/sql
	* @param p_username The username to authenticate REST Enabled SQL service. Ex: DEMO
	* @param p_password The password to authenticate REST Enabled SQL service. Ex: demoPassword
	* @param p_sql The sql statement that is to be run using REST Enabled SQL service. Ex: select 'Hello World' as myCol from dual
	* @returns CLOB A sql query which utilizes JSON_TABLE and this package to return a result set from rest enabled sql
	**/
	FUNCTION rest_query(
	p_url VARCHAR2, --rest enabled sql url
	p_username VARCHAR2, --username
	p_password VARCHAR2, --password
	p_sql CLOB)
	RETURN CLOB;--sql statement to run
	/**
	* An Oracle view is created using restsql.rest_query to generate a sql query which provides a result set of the rest enabled sql response.
	* This Oracle view can then be referenced in SQL without any knownledge that the data is being gathered from the result of a rest enabled sql request.
	*
	* SQL*Plus Ex:
	* exec restsql.request_view('REMOTE_ALL_TABLES','https://localhost:8087/ords/demouser/_/sql','DEMO','demopassword',q'!select * from all_tables!');
	* select DBMS_METADATA.GET_DDL('VIEW','REMOTE_ALL_TABLES') from DUAL;
	* select * from REMOTE_ALL_TABLES;
	*
	* @param p_url The REST Enabled SQL url. Ex: https://localhost:8087/ords/demouser/_/sql
	* @param p_username The username to authenticate REST Enabled SQL service. Ex: DEMO
	* @param p_password The password to authenticate REST Enabled SQL service. Ex: demoPassword
	* @param p_sql The sql statement that is to be run using REST Enabled SQL service. Ex: select 'Hello World' as myCol from dual
	**/
	PROCEDURE rest_view(
	p_viewname VARCHAR2,
	p_url VARCHAR2,
	p_username VARCHAR2,
	p_password VARCHAR2,
	p_sql CLOB);
	END restsql;

view raw restsql.pks hosted with ❤ by GitHub

restsql.pkb

	create or replace PACKAGE BODY "RESTSQL"
	AS
	/**
	* Project: RESTSQL utilities
	* Description: Package which works with ORDS to provide SQL access to REST services
	* Note: SQL*Plus examples print out CLOB. Best to use the following so the CLOB values are displayed correctly in SQL*Plus
	* set long 4000
	* set pagesize 1000
	* @author: dermot.oneill@oracle.com
	*/
	/**
	* Reads the http response into a CLOB
	*/
	FUNCTION read_response(
	resp IN OUT utl_http.resp )
	RETURN CLOB
	AS
	BUFFER VARCHAR2(32767);
	responsebody CLOB:= ' ';
	eob BOOLEAN;
	BEGIN
	eob := false; -- END-OF-BODY flag (Boolean)
	WHILE NOT(eob)
	LOOP
	BEGIN
	utl_http.read_text(resp, buffer, 32767); -- buffer = VARCHAR2(32767)
	IF buffer IS NOT NULL AND LENGTH(buffer)>0 THEN
	dbms_lob.writeappend(responsebody, LENGTH(buffer), buffer);
	END IF;
	EXCEPTION
	WHEN UTL_HTTP.END_OF_BODY THEN
	eob := true;
	END;
	END LOOP;
	utl_http.end_response(resp);
	RETURN responsebody;
	END;
	/**
	* Generates the JSON_TABLE COLUMN clause using the metadata from the rest enabled sql response and returns it withing a CLOB
	* First it performs a http request to the rest enabled sql service.
	* Gets the JSON response and using JSON_TABLE references the metadata information about the column names and data types.
	* Using this information it generates a JSON_TABLE COLUMN clause.
	* This JSON_TABLE COLUMN clause can be used in new JSON_TABLE queries to transform the rest enabled sql response into sql columns
	*/
	FUNCTION rest_column_def(
	p_url VARCHAR2,
	p_username VARCHAR2,
	p_password VARCHAR2,
	p_sql CLOB)
	RETURN CLOB
	AS
	v_columns CLOB := 'COLUMNS(';
	v_columnDetails VARCHAR2(4000);
	v_columnIsNull VARCHAR2(4000);
	v_columnPath VARCHAR2(4000);
	v_notFirstLoop BOOLEAN := FALSE;
	BEGIN
	FOR metadata IN
	(SELECT columnname,
	columntypename,
	jsonColumnName,
	PRECISION,
	scale,
	isNullable
	FROM JSON_TABLE( request(p_url, --rest enabled sql url
	p_username, --username
	p_password, --password
	p_sql), --sql query
	'$.items.resultSet.metadata[*]' --path to resultset metadata
	COLUMNS(columnName VARCHAR2(4000) PATH '$.columnName', jsonColumnName VARCHAR2(4000) PATH '$.jsonColumnName', columnTypeName VARCHAR2(4000) PATH '$.columnTypeName', PRECISION NUMBER PATH '$.precision', scale NUMBER PATH '$.scale', isNullable NUMBER PATH '$.isNullable'))
	)
	LOOP
	v_columnDetails := '';
	v_columnIsNull := '';
	IF metadata.isNullable = 1 THEN
	v_columnIsNull := ' NULL ';
	ELSE
	v_columnIsNull := ' NOT NULL ';
	END IF;
	IF metadata.precision != 0 AND metadata.scale != 0 THEN
	v_columnDetails := '(' || metadata.precision || ',' || metadata.scale || ')';
	ELSIF metadata.precision != 0 THEN
	v_columnDetails :='('||metadata.precision||')';
	END IF;
	IF v_notFirstLoop THEN
	v_columns := v_columns || ',';
	END IF;
	v_columnPath := q'! PATH '$.!'||metadata.jsonColumnName||q'!'!';
	v_columns :=v_columns ||CHR(10)|| ' "'||metadata.columnName || '" ' || metadata.columnTypeName|| v_columnDetails || '' || v_columnPath ;
	v_notFirstLoop :=TRUE;
	END LOOP metadata;
	v_columns :=v_columns ||')';
	return v_columns;
	END;
	/**
	* Performs a http request to the rest enabled sql service and returns the result as a clob
	*
	* SQL*Plus Ex:
	* var v_response CLOB;
	* exec :v_response := restsql.request('https://localhost:8087/ords/demouser/_/sql','DEMO','demopassword',q'!select 'Hello World' as myCol from dual!');
	* print :v_response;
	*
	* @param p_url The REST Enabled SQL url. Ex: https://localhost:8087/ords/demouser/_/sql
	* @param p_username The username to authenticate REST Enabled SQL service. Ex: DEMO
	* @param p_password The password to authenticate REST Enabled SQL service. Ex: demoPassword
	* @param p_sql The sql statement that is to be run using REST Enabled SQL service. Ex: select 'Hello World' as myCol from dual
	* @returns CLOB The JSON response from the rest enabled sql service
	**/
	FUNCTION request(
	p_url VARCHAR2,
	p_username VARCHAR2,
	p_password VARCHAR2,
	p_sql CLOB)
	RETURN CLOB
	AS
	v_sql VARCHAR2(4000) := p_sql;
	req utl_http.req;
	resp utl_http.resp;
	v_auth VARCHAR2(4000);
	BEGIN
	req := utl_http.begin_request(p_url, 'POST',' HTTP/1.1');
	v_auth :='Basic ' || utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((p_username||':'||p_password))));
	utl_http.set_header(req, 'Authorization', v_auth);
	utl_http.set_header(req, 'Content-Type', 'application/sql');
	utl_http.set_header(req, 'Content-Length', lengthb(v_sql));
	utl_http.write_text(req, v_sql);
	resp := utl_http.get_response(req);
	RETURN read_response(resp);
	END;
	/**
	* Performs a http request to the rest service and returns the result as a clob
	*
	* SQL*Plus Ex:
	* var v_response CLOB;
	* exec :v_response := restsql.request('https://localhost:8087/ords/demouser/myview/');
	* print :v_response;
	*
	* @param p_url The REST url. Ex: https://localhost:8087/ords/demouser/myview/
	* @param p_username The username to authenticate REST service. Ex: DEMO
	* @param p_password The password to authenticate REST service. Ex: demoPassword
	* @returns CLOB The JSON response from the rest service
	**/
	FUNCTION request(
	p_url VARCHAR2,
	p_username VARCHAR2 DEFAULT NULL,
	p_password VARCHAR2 DEFAULT NULL)
	RETURN CLOB
	AS
	req utl_http.req;
	resp utl_http.resp;
	v_auth VARCHAR2(4000);
	BEGIN
	req := utl_http.begin_request(p_url, 'GET',' HTTP/1.1');
	IF p_username IS NOT NULL THEN
	v_auth :='Basic ' || utl_raw.cast_to_varchar2(utl_encode.base64_encode(utl_raw.cast_to_raw((p_username||':'||p_password))));
	utl_http.set_header(req, 'Authorization', v_auth);
	END IF;
	resp := utl_http.get_response(req);
	RETURN read_response(resp);
	END;
	/**
	* Performs a http request to the rest enabled sql service and using the metadata in the response, generates a sql query which can be used to access the result of the rest enabled sql service in the future
	* The sql query is returned as a clob, which can then be saved, used to "create view" or executed immediately
	* The sql query returned uses Oracles JSON_TABLE syntax to represent the JSON response from rest enabled sql as a table.
	* This is useful when you want to combine the response of the rest enabled sql service within a SQL statement.
	*
	* SQL*Plus Ex:
	* var v_response CLOB;
	* exec :v_response := restsql.request_query('https://localhost:8087/ords/demouser/_/sql','DEMO','demopassword',q'!select 'Hello World' as myCol from dual!');
	* print :v_response;
	*
	* @param p_url The REST Enabled SQL url. Ex: https://localhost:8087/ords/demouser/_/sql
	* @param p_username The username to authenticate REST Enabled SQL service. Ex: DEMO
	* @param p_password The password to authenticate REST Enabled SQL service. Ex: demoPassword
	* @param p_sql The sql statement that is to be run using REST Enabled SQL service. Ex: select 'Hello World' as myCol from dual
	* @returns CLOB A sql query which utilizes JSON_TABLE and this package to return a result set from rest enabled sql
	**/
	FUNCTION rest_query(
	p_url VARCHAR2,
	p_username VARCHAR2,
	p_password VARCHAR2,
	p_sql CLOB)
	RETURN CLOB
	AS
	v_query CLOB;
	BEGIN
	v_query := q'!SELECT * FROM JSON_TABLE ( !' || CHR(10) ||
	q'!restsql.request('!' ||p_url||q'!', !' ||CHR(10) ||
	q'!'!'||p_username||q'!','!' || p_password ||q'!',!'||
	q'! q'#!'||p_sql||q'!#'),'!' ||restsql.resultPath||q'!' !';
	--create the column definition
	v_query := v_query || CHR(10) || rest_column_def(p_url,p_username,p_password,p_sql) || ')';
	return v_query;
	END;
	/**
	* An Oracle view is created using restsql.rest_query to generate a sql query which provides a result set of the rest enabled sql response.
	* This Oracle view can then be referenced in SQL without any knownledge that the data is being gathered from the result of a rest enabled sql request.
	*
	* SQL*Plus Ex:
	* exec restsql.request_view('REMOTE_ALL_TABLES','https://localhost:8087/ords/demouser/_/sql','DEMO','demopassword',q'!select * from all_tables!');
	* select DBMS_METADATA.GET_DDL('VIEW','REMOTE_ALL_TABLES') from DUAL;
	* select * from REMOTE_ALL_TABLES;
	*
	* @param p_url The REST Enabled SQL url. Ex: https://localhost:8087/ords/demouser/_/sql
	* @param p_username The username to authenticate REST Enabled SQL service. Ex: DEMO
	* @param p_password The password to authenticate REST Enabled SQL service. Ex: demoPassword
	* @param p_sql The sql statement that is to be run using REST Enabled SQL service. Ex: select 'Hello World' as myCol from dual
	**/
	PROCEDURE rest_view(
	p_viewname VARCHAR2,
	p_url VARCHAR2,
	p_username VARCHAR2,
	p_password VARCHAR2,
	p_sql CLOB)
	AS
	v_ddl CLOB:= '';
	BEGIN
	v_ddl := 'CREATE VIEW '||p_viewname||' AS ' ||rest_query(p_url,p_username,p_password,p_sql);
	dbms_output.put_line(v_ddl);
	EXECUTE immediate v_ddl;
	END;
	END restsql;

view raw restsql.pkb hosted with ❤ by GitHub

Setup HTTP access in Oracle 12c:
You may need to setup HTTP access in your database. Heres how I went about providing access to my Oracle user TESTREPO

	BEGIN
	DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
	acl => 'testrepodev.xml',
	description => 'Permissions to access internet',
	principal => 'TESTREPO',
	is_grant => TRUE,
	privilege => 'connect',
	start_date => SYSTIMESTAMP,
	end_date => NULL);
	COMMIT;
	END;
	/
	begin
	DBMS_NETWORK_acl_ADMIN.ADD_PRIVILEGE(
	acl => 'testrepodev.xml',
	principal => 'TESTREPO',
	is_grant => true,
	privilege => 'resolve'
	);
	COMMIT;
	END;
	/
	BEGIN
	DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
	acl => 'testrepodev.xml',
	host => '*');
	COMMIT;
	END;
	/

view raw http_access.sql hosted with ❤ by GitHub

Example of a view automatically created by restsql.rest_view:

EXEC restsql.rest_view('REMOTE_ALL_TABLES','http://10.175.200.185:8087/ords/demo/_/sql','DEMO','demo','select * from all_tables');

	CREATE VIEW "REMOTE_ALL_TABLES" ("OWNER", "TABLE_NAME", "TABLESPACE_NAME", "CLUSTER_NAME", "IOT_NAME", "STATUS", "PCT_FREE", "PCT_USED", "INI_TRANS", "MAX_TRANS", "INITIAL_EXTENT", "NEXT_EXTENT", "MIN_EXTENTS", "MAX_EXTENTS", "PCT_INCREASE", "FREELISTS", "FREELIST_GROUPS", "LOGGING", "BACKED_UP", "NUM_ROWS", "BLOCKS", "EMPTY_BLOCKS", "AVG_SPACE", "CHAIN_CNT", "AVG_ROW_LEN", "AVG_SPACE_FREELIST_BLOCKS", "NUM_FREELIST_BLOCKS", "DEGREE", "INSTANCES", "CACHE", "TABLE_LOCK", "SAMPLE_SIZE", "LAST_ANALYZED", "PARTITIONED", "IOT_TYPE", "TEMPORARY", "SECONDARY", "NESTED", "BUFFER_POOL", "FLASH_CACHE", "CELL_FLASH_CACHE", "ROW_MOVEMENT", "GLOBAL_STATS", "USER_STATS", "DURATION", "SKIP_CORRUPT", "MONITORING", "CLUSTER_OWNER", "DEPENDENCIES", "COMPRESSION", "COMPRESS_FOR", "DROPPED", "READ_ONLY", "SEGMENT_CREATED", "RESULT_CACHE", "CLUSTERING", "ACTIVITY_TRACKING", "DML_TIMESTAMP", "HAS_IDENTITY", "CONTAINER_DATA", "INMEMORY", "INMEMORY_PRIORITY", "INMEMORY_DISTRIBUTE", "INMEMORY_COMPRESSION", "INMEMORY_DUPLICATE", "DEFAULT_COLLATION", "DUPLICATED", "SHARDED", "EXTERNAL", "CELLMEMORY", "CONTAINERS_DEFAULT", "CONTAINER_MAP", "EXTENDED_DATA_LINK", "EXTENDED_DATA_LINK_MAP", "INMEMORY_SERVICE", "INMEMORY_SERVICE_NAME", "CONTAINER_MAP_OBJECT") AS
	SELECT "OWNER","TABLE_NAME","TABLESPACE_NAME","CLUSTER_NAME","IOT_NAME","STATUS","PCT_FREE","PCT_USED","INI_TRANS","MAX_TRANS","INITIAL_EXTENT","NEXT_EXTENT","MIN_EXTENTS","MAX_EXTENTS","PCT_INCREASE","FREELISTS","FREELIST_GROUPS","LOGGING","BACKED_UP","NUM_ROWS","BLOCKS","EMPTY_BLOCKS","AVG_SPACE","CHAIN_CNT","AVG_ROW_LEN","AVG_SPACE_FREELIST_BLOCKS","NUM_FREELIST_BLOCKS","DEGREE","INSTANCES","CACHE","TABLE_LOCK","SAMPLE_SIZE","LAST_ANALYZED","PARTITIONED","IOT_TYPE","TEMPORARY","SECONDARY","NESTED","BUFFER_POOL","FLASH_CACHE","CELL_FLASH_CACHE","ROW_MOVEMENT","GLOBAL_STATS","USER_STATS","DURATION","SKIP_CORRUPT","MONITORING","CLUSTER_OWNER","DEPENDENCIES","COMPRESSION","COMPRESS_FOR","DROPPED","READ_ONLY","SEGMENT_CREATED","RESULT_CACHE","CLUSTERING","ACTIVITY_TRACKING","DML_TIMESTAMP","HAS_IDENTITY","CONTAINER_DATA","INMEMORY","INMEMORY_PRIORITY","INMEMORY_DISTRIBUTE","INMEMORY_COMPRESSION","INMEMORY_DUPLICATE","DEFAULT_COLLATION","DUPLICATED","SHARDED","EXTERNAL","CELLMEMORY","CONTAINERS_DEFAULT","CONTAINER_MAP","EXTENDED_DATA_LINK","EXTENDED_DATA_LINK_MAP","INMEMORY_SERVICE","INMEMORY_SERVICE_NAME","CONTAINER_MAP_OBJECT" FROM JSON_TABLE (
	restsql.request('http://10.175.200.185:8087/ords/demo/_/sql',
	'DEMO','demo', q'#select * from all_tables#'),'$.items.resultSet.items[*]'
	COLUMNS(
	"OWNER" VARCHAR2(128) PATH '$.owner',
	"TABLE_NAME" VARCHAR2(128) PATH '$.table_name',
	"TABLESPACE_NAME" VARCHAR2(30) PATH '$.tablespace_name',
	"CLUSTER_NAME" VARCHAR2(128) PATH '$.cluster_name',
	"IOT_NAME" VARCHAR2(128) PATH '$.iot_name',
	"STATUS" VARCHAR2(8) PATH '$.status',
	"PCT_FREE" NUMBER PATH '$.pct_free',
	"PCT_USED" NUMBER PATH '$.pct_used',
	"INI_TRANS" NUMBER PATH '$.ini_trans',
	"MAX_TRANS" NUMBER PATH '$.max_trans',
	"INITIAL_EXTENT" NUMBER PATH '$.initial_extent',
	"NEXT_EXTENT" NUMBER PATH '$.next_extent',
	"MIN_EXTENTS" NUMBER PATH '$.min_extents',
	"MAX_EXTENTS" NUMBER PATH '$.max_extents',
	"PCT_INCREASE" NUMBER PATH '$.pct_increase',
	"FREELISTS" NUMBER PATH '$.freelists',
	"FREELIST_GROUPS" NUMBER PATH '$.freelist_groups',
	"LOGGING" VARCHAR2(3) PATH '$.logging',
	"BACKED_UP" VARCHAR2(1) PATH '$.backed_up',
	"NUM_ROWS" NUMBER PATH '$.num_rows',
	"BLOCKS" NUMBER PATH '$.blocks',
	"EMPTY_BLOCKS" NUMBER PATH '$.empty_blocks',
	"AVG_SPACE" NUMBER PATH '$.avg_space',
	"CHAIN_CNT" NUMBER PATH '$.chain_cnt',
	"AVG_ROW_LEN" NUMBER PATH '$.avg_row_len',
	"AVG_SPACE_FREELIST_BLOCKS" NUMBER PATH '$.avg_space_freelist_blocks',
	"NUM_FREELIST_BLOCKS" NUMBER PATH '$.num_freelist_blocks',
	"DEGREE" VARCHAR2(40) PATH '$.degree',
	"INSTANCES" VARCHAR2(40) PATH '$.instances',
	"CACHE" VARCHAR2(20) PATH '$.cache',
	"TABLE_LOCK" VARCHAR2(8) PATH '$.table_lock',
	"SAMPLE_SIZE" NUMBER PATH '$.sample_size',
	"LAST_ANALYZED" DATE PATH '$.last_analyzed',
	"PARTITIONED" VARCHAR2(3) PATH '$.partitioned',
	"IOT_TYPE" VARCHAR2(12) PATH '$.iot_type',
	"TEMPORARY" VARCHAR2(1) PATH '$.temporary',
	"SECONDARY" VARCHAR2(1) PATH '$.secondary',
	"NESTED" VARCHAR2(3) PATH '$.nested',
	"BUFFER_POOL" VARCHAR2(7) PATH '$.buffer_pool',
	"FLASH_CACHE" VARCHAR2(7) PATH '$.flash_cache',
	"CELL_FLASH_CACHE" VARCHAR2(7) PATH '$.cell_flash_cache',
	"ROW_MOVEMENT" VARCHAR2(8) PATH '$.row_movement',
	"GLOBAL_STATS" VARCHAR2(3) PATH '$.global_stats',
	"USER_STATS" VARCHAR2(3) PATH '$.user_stats',
	"DURATION" VARCHAR2(15) PATH '$.duration',
	"SKIP_CORRUPT" VARCHAR2(8) PATH '$.skip_corrupt',
	"MONITORING" VARCHAR2(3) PATH '$.monitoring',
	"CLUSTER_OWNER" VARCHAR2(128) PATH '$.cluster_owner',
	"DEPENDENCIES" VARCHAR2(8) PATH '$.dependencies',
	"COMPRESSION" VARCHAR2(8) PATH '$.compression',
	"COMPRESS_FOR" VARCHAR2(30) PATH '$.compress_for',
	"DROPPED" VARCHAR2(3) PATH '$.dropped',
	"READ_ONLY" VARCHAR2(3) PATH '$.read_only',
	"SEGMENT_CREATED" VARCHAR2(3) PATH '$.segment_created',
	"RESULT_CACHE" VARCHAR2(7) PATH '$.result_cache',
	"CLUSTERING" VARCHAR2(3) PATH '$.clustering',
	"ACTIVITY_TRACKING" VARCHAR2(23) PATH '$.activity_tracking',
	"DML_TIMESTAMP" VARCHAR2(25) PATH '$.dml_timestamp',
	"HAS_IDENTITY" VARCHAR2(3) PATH '$.has_identity',
	"CONTAINER_DATA" VARCHAR2(3) PATH '$.container_data',
	"INMEMORY" VARCHAR2(8) PATH '$.inmemory',
	"INMEMORY_PRIORITY" VARCHAR2(8) PATH '$.inmemory_priority',
	"INMEMORY_DISTRIBUTE" VARCHAR2(15) PATH '$.inmemory_distribute',
	"INMEMORY_COMPRESSION" VARCHAR2(17) PATH '$.inmemory_compression',
	"INMEMORY_DUPLICATE" VARCHAR2(13) PATH '$.inmemory_duplicate',
	"DEFAULT_COLLATION" VARCHAR2(100) PATH '$.default_collation',
	"DUPLICATED" VARCHAR2(1) PATH '$.duplicated',
	"SHARDED" VARCHAR2(1) PATH '$.sharded',
	"EXTERNAL" VARCHAR2(3) PATH '$.external',
	"CELLMEMORY" VARCHAR2(24) PATH '$.cellmemory',
	"CONTAINERS_DEFAULT" VARCHAR2(3) PATH '$.containers_default',
	"CONTAINER_MAP" VARCHAR2(3) PATH '$.container_map',
	"EXTENDED_DATA_LINK" VARCHAR2(3) PATH '$.extended_data_link',
	"EXTENDED_DATA_LINK_MAP" VARCHAR2(3) PATH '$.extended_data_link_map',
	"INMEMORY_SERVICE" VARCHAR2(12) PATH '$.inmemory_service',
	"INMEMORY_SERVICE_NAME" VARCHAR2(1000) PATH '$.inmemory_service_name',
	"CONTAINER_MAP_OBJECT" VARCHAR2(3) PATH '$.container_map_object'));

view raw rest_enabled_sql_view.sql hosted with ❤ by GitHub

Limitations:
The view is limited to the max number of rows returned by ORDS as I have not implemented automatic pagination in the view.

Lots can be done with ORDS and JSON_TABLE. Stay tuned!