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

restsql.pks

restsql.pkb

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

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


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!