Updated: 9 February 2024
You know what SQLcl is.
You know what ORDS is.
How do those two things go together?
If you’re a Java developer, you can create JDBC connections using our ORDS mid-tier…that means your Java apps can interact with your Oracle Database via ORDS and HTTPS, vs directly on the database and stateful connections via the Listener.
Wanna play?
Maybe the easiest way is to connect to ORDS with SQLcl – using a JDBC driver we built that ‘talks REST.’
- Get ORDS
- Configure it to allow for the new _/SQL/ endpoint – make sure REST Enable SQL is turned ON
- 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 beers/password@https://some-server.com/ords/beers/
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 using authentication. Once authenticated, you’ll ONLY be authorized to hit the /SQL/ endpoint in THAT REST enabled schema (BEERS).
So, when running stuff through this connection, it will ALWAYS be as BEERS. 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…
If you’re in your Java app, you’ll use this to reference the driver –
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/.
But in SQLcl, it’s much easier, if we see a HTTPS address, we’ll assume you’re wanting to use the REST Driver.
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.
We also have this project on GitHub. It allows you to run stuff over the new endpoint in ORDS via a webpage.
