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



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','','DEMO','demo','select * from all_tables');

The view is limited to the max number of rows returned by ORDS as I …

Getting Started with REST Enabled SQL

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

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.

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…

Temporarily Disable a Generated Always As Identity Column

Oracle Database 12c allows you to define a column to be an IDENTITY column.

A GENERATED ALWAYS AS IDENTITY column does not allow you to INSERT a value for it.
When it comes to moving data from one table to another and this type of column is present, there are a few steps to "disable" the GENERATED ALWAYS AS IDENTITY column and after the data move "enable" it.

set echo on drop table test_identity; create table test_identity(id number generated always as identity); --This insert will fail as the column is generated always --and it does not accept values insert into test_identity(id) values(1); --Change the identity column to generated by default. --This will allow us to insert our values alter table test_identity modify id generated by default as identity; --This insert works. as will Copy to Oracle insert into test_identity(id) values(1); select * from test_identity; --Change the identity column back to gener…

SQLcl Aliases & The Invisible Column Trick

SQLcl is the bee's knees.

Problem Today I had a common problem. I wanted to reposition a column within a table. "Oh you should use views!"  "Never reference a table directly!"  "Column position should be meaningless!" I hear you shout. Yes but, look at this table definition.
PERSON table

FIRSTNAME,ADDRESS,PHONE,LASTNAME Doesn't that drive you mad. 
I want the order FIRSTNAME, LASTNAME, ADDRESS, PHONE Or worse. You decide to add a new column  (middlename) to your table definition script. create table person (firstname varchar2(100),                             middlename varchar2(100),                                  lastname varchar2(100),                                     address varchar2(100),                                       phone varchar2(100));
But you need to update the table right now alter table person add (middlename varchar2(100)); Your table definition script will generated a different table from your live table as the live tables &…

Migrating To Oracle Using Custom Object Names

Sybase , SQL  Server and other databases allow for long identifier names.
Oracle allows for a maximum of 30 Bytes when naming objects like  users, tables, ...
When it comes to migrating objects to Oracle,  SQL Developer will truncate the object names and resolve clashes with unique names.

Say for example you have two tables called


Oracle SQL Developer will convert these to

Application_Name_SubArea_Name_SApplication_Name_SubArea_Name_1 This default new name may not be to your liking, so SQL Developer has an easy way of changing this mapping.
First perform the Capture and Convert phases. You do not need to perform all the phases of a migration as the migration wizard allows you to stop and start a migration at many points. In this case click "Finish" on the Convert page of the migration wizard.
Once the capture and convert is complete, we will have a list of al…

Oracle 12c Implicit Result Sets in SQL Developer 4.1

Ever want to run a Stored Procedure or a PL/SQL anonymous block and just want to "print out" the results of a query ?  Even as a little bit of debug information?

In Oracle 11g you have to create a SQL*Plus REFCURSOR variable and then bind it within the anonymous block  or  pass it as an argument to a procedure/function. Run the code and then print the refcursor.

This requires a bit of know how in SQL*Plus and how it will work with your PL/SQL block or procedure.

In Oracle 12c a new feature called IMPLICIT RESULT SETS allows you to pass back a result set without parameters or external binds. When calling from SQL*Plus or SQL Developer Worksheet , this means we do not have to create REFCURSOR variables.

It was initially developed to help migrating from Sybase and SQL Server to Oracle. But its a nice feature which may help you in your day to day.

Explicit Result Sets in 11g

 Implicit Result Sets in 12c

In Oracle 12c the cursor is defined as variable not a parameter. This cursor v…