Posts

Showing posts from September, 2017

A REST Enabled SQL View!

Image
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 …

Getting Started with REST Enabled SQL

Image
POST a query or DML or DDL or even a short script over HTTPS to ORDS and have ORDS run that on your Oracle Database and have the results returned in JSON. That's the plan.
What You Need To KnowREST Enabled SQL uses Schema Authentication as well First Party Authentication.  



This means you can run SQL against an Oracle Database if the following is true. REST Enabled SQL is enabled in ORDSA database user is REST Enabled The database user name is knownThe database user password is knownSo before turning on REST Enabled SQL in ORDS it is vital that current REST Enabled schemas havestrong, secure passwords.

This should be resolved before turning on REST Enabled SQL in ORDS, as once it is turned on, all REST Enabled schemas will be accessible using only their database username and password through ORDS. The same applies for any new REST Enabled schemas. Setup Requirements Download ORDS 17.3.Download cURL . Not necessary, but the examples use cURLDownload Java 1.8 Access to an Oracle Databas…

New! REST Enabled SQL for ORDS

Image
The Oracle REST Data Service provides REST access to your Oracle Database. Tables, predefined queries and PL/SQL blocks can be exposed as RESTful services. This is great when you can foresee what table, query or action you wish to REST enable.

Starting in ORDS 17.3 you can now POST the query, pl/sql or sql*plus statement to ORDS at run time. This new feature is call REST Enabled SQL and it is built into ORDS as standard. It allows you to define the query or statement you want to run without having to create a predefined RESTFul service.

Download
http://www.oracle.com/technetwork/developer-tools/rest-data-services/downloads/ords-beta-173-3873522.html
REST Enabled SQL provides a HTTPS POST API that your HTTP client (web browser, java application, ...) can access just like other ORDS services. Just POST the SQL statement you wish to run to the REST Enabled SQL endpoint and the results will be returned in JSON.
Example Web Form A HTML form POSTs to the REST Enabled SQL endpoint* a stateme…