You know what SQLcl is.
You know what ORDS is.
How do those two things go together?
Well, in ORDS 17.3 (Early Adopter/BETA!), we offer a new feature that allows you to execute ad hoc SQL and SQL scripts via HTTPS POST calls to ORDS.
No access to the database via the Listener required.
Wanna play?
Maybe the easiest way is to connect to ORDS with SQLcl – using a JDBC driver we built that ‘talks REST.’
- Get ORDS 17.3
- Configure it to allow for the new _/SQL/ endpoint
- REST enable a SCHEMA
- Start ORDS
Kris talks about how to do all of this this here.
Download the REST driver. — Same page as the ORDS link, just scroll down and click download next to ‘Oracle REST Data Services JDBC driver.’
That’s a JAR file.
Drop it into your SQLcl/lib directory – you’ll see a TON of other JAR files there.
Start SQLcl.
Let’s look at that connect string.
connect HR/oracle@jdbc:oracle:orest:@http://localhost:8888/ords/hr/
The first part, you’re used to.
Oracle DB User/Password
That’s normal for the Database, but NOT normal for authenticating in ORDS.
In ORDS you’d normally authenticate via your webserver user or via OATH2. But for this feature, we’ve added database authentication. So that’s brand new for ORDS. Once authenticated, you’ll ONLY be able to hit the /SQL/ endpoint in THAT REST enabled schema.
So, when running stuff through this connection, it will ALWAYS be as HR. That’s how ORDS works, it proxy connects (a JDBC thing) from the ORDS_PUBLIC_USER to the user on the REST enabled SCHEMA when running the work behind the REST POST, PUT, GET, DELETE, HEAD…
jdbc:oracle:orest:
This is how you’ll specify the new REST driver you just downloaded.
@http://localhost:8888/ords/hr/
This is the webserver address where ORDS is running, followed by the /ORDS/schema/.
You can ALSO do this.
In this case, the ‘dev’ user is an ORDS/Jetty user that’s been granted the SQL Developer role.
THIS user can hit ANY REST enabled SQL endpoint. So she can run stuff as HR or as ORDS_DEMO – both schemas which have been REST enabled.
Run your stuff
We support a LOT of stuff on this new JDBC driver. You can see the list here.
REST is stateless. That means you’re in AUTOCOMMIT mode. You run an UPDATE – it gets committed at the end of the call. There’s no ROLLBACK. Each request you make, each statement you run, that’s a separate call to ORDS and transaction.
You can’t touch the OS. Don’t think you can connect to ORDS with SQLcl and use a HOST command to take over a server. That’s all locked down. No SPOOLING. But you can run a INFO/DESC, a PL/SQL block, and of course your go to for all things data – SQL.
Why are we doing this?
A few reasons.
Folks need/want access to databases that aren’t regularly available for database connections.
We wanted to build a webified version of SQL Developer that runs in your browser and does all the database work via REST (powered by ORDS) – so we kind of needed a REST endpoint that handles ad hoc SQL, PL/SQL, scripts, and even SQL*Plus/SQLcl commands.
You might want to make data available to your app that’s lying in another database WITHOUT using DB_LINKS. So you could do that now via a POST call assuming that other database is configured for ORDS.
This is ALL early adopter BETA. Don’t run it in production. Let us know what you think on the forums. And check out ORACLE-BASE’s article on the new ORDS feature.
We also have this project on GitHub. It allows you to run stuff over the new endpoint in ORDS via a webpage.
