Dermot's Tech Tips

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

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 Know REST 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 ORDS A database user is REST Enabled  The database user name is known The database user password is known So before turning on REST Enabled SQL in ORDS it is vital that current REST Enabled schemas have strong, 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 cUR

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 endp