ORDS, SQLcl and SQL Developer 18.3 Updates (VirtualBox, Vagrant, Docker)

A few days ago we got version 18.3 of a bunch of Oracle tools.

Over the weekend I updated some of my VirtualBox and Vagrant builds to include these versions. If you want to play around with them you can see them on GitHub here.

I also updated my ORDS Docker container build, which uses both ORDS and SQLcl. You can find this on GitHub here.

I use this container for live demos of ORDS, as well as a demo for my “DBA Does Docker” talk, which I am doing at Oracle OpenWorld this year.

I put the latest versions of SQL Developer and SQLcl on my laptop. I’m doing an analytic functions talk at Oracle Code One this year. The demos use SQLcl on my laptop connecting to Autonomous Transaction Proccessing (ATP) on Oracle Cloud. I had a little bit of drama with SQLcl on Saturday, which turned out to be PEBCAK. I thought “SET ECHO ON” wasn’t working, but it turned out I had a “login.sql” file in the path that contained “SET TERMOUT OFF”. Once I removed that setting the demos ran fine. 🙂

I’m going to put a freeze on changing my stuff until after OpenWorld and Code One. Honest. 🙂

Cheers

Tim…

ORDS, SQL Developer and SQLcl Version 18.1 Released : Plus Some ORDS Documentation Comments

If you’re active in the Twitter-verse you will have seen a bunch of tweets yesterday about the release of the 18.1.x versions of Oracle REST Data Services (ORDS), SQL Developer (and Data Modeler) and SQLcl.

The first thing I did was edit my ORDS Docker build to use the latest versions of ORDS, SQLcl, Tomcat9 and Java9. If you are interested in playing with that you can find the build on my GitHub here. It was all smooth sailing! 🙂

There are a couple of things I would like to point out about the ORDS 18.1 documentation.

ORDS Installation in Multitenant

The way I read it, the documentation suggests there are two ways of installing ORDS in a multitentant environment, both of which involve installing it into the CDB.

Multitenant is an architecture, so it doesn’t imply the presence of the Multitenant option, or multiple PDBs in the CDB. In fact, I would suggest the vast majority of CDBs will only ever contain a single PDB, as most instances will eventually switch to Lone-PDB (for free) now that non-CDB is deprecated. So what does this have to do with the installations described above?

The advantage of installing ORDS in the CDB is you can have a single connection pool for all PDBs in the instance, which is a big advantage if you have 4096 PDBs in your CDB. If you only have one PDB per CDB, there is no advantage, and actually there is a disadvantage because the PDB has yet another dependency on the CDB, which means ORDS must be installed in the other CDBs before you clone/relocate a PDB to them.

In my opinion, the best way to install ORDS for Lone-PDB, and possibly even for small numbers of PDBs in one instance, is to install directly into the PDB, just like we do with APEX. This means each PDB needs its own connection pool in ORDS, but that’s not a problem. This way the PDB doesn’t have an external ORDS dependency on the container and can be moved between containers without any fuss.

You will see my ORDS installation article installs directly into the PDB, although I will be amending it with some comments about other options.

Database Authentication for PL/SQL Gateway Calls

One of the new features of ORDS 18.1 is you can now use database authentication to provide basic authentication for your calls to PL/SQL. You won’t see that in the documentation though. It’s only present in the examples under the “/path/to/ords/examples/db_auth” directory when you unzip the ORDS media.

Using this type of authentication is not advisable, it’s much better to use OAuth2, but for people like me that have a lot of applications and XML web services still using mod_plsql, this is a really handy feature, and certainly eases the transition from mod_plsql to ORDS.

It would be nice if this feature were put into the main documentation, as it will be a welcome addition for many people out there. It is a feature, so it should be documented as such, and anything that improves the uptake of ORDS has got to be a good thing.

How’s about including the document upload/download functionality from mod_plsql in ORDS also? That would help with the transition too. Yes, I know it’s easy to code this yourself, but that is still an application change, rather than just switching from one gateway to another. Just sayin’. 🙂

I’ll be playing around with ORDS 18 over the coming weeks. I’ll probably amend some of my existing ORDS articles as a result of that, and no doubt put out a new article on database authentication, for the lost souls that don’t look at the media examples, like me before Jeff told me to. 🙂

Cheers

Tim…

SQLcl : Going cold turkey from SQL*Plus

I’m a SQL*Plus junkie. I have loads of scripts that do exactly what I want them to do, so for most tasks I am more productive from the command line than using other tools. If I do find something difficult, I just flick across to SQL Developer or Cloud Control. I’m not a snob about it, but I prefer the command line.

A couple of weeks ago I was playing with the ORDS stuff in SQLcl.

That made me think I really should make the switch, but I didn’t. Then I had to dump something out as CSV, and SQLcl makes that really easy.

I forgot the 12.2 SQL*Plus client I was using could also do this, even though I had written about it (here), but the SQLcl formatting is more flexible anyway. 🙂

So after that I decided to try an experiment and go cold turkey from SQL*Plus and only use SQLcl. I think today is the 1 week anniversary. 🙂

The only real problem has been muscle-memory. My hand naturally types “sqlplus” rather than “sql”. It’s going to take a long time to get out of that habit. 🙂

I’m not using all the extra features all the time, so I can’t claim it’s revolutionised my life, but having them at hand is certainly … handy. 🙂

I’ve also started using it on some of my application servers. On some of them I have to check for the presence of the database before starting the application server. Just the standard race condition stuff you get during failover events. I was using the instant client before, but I’ve switched to SQLcl now because it is smaller. That’s especially nice on Docker.

So that’s one week down and I’ve got no plans (yet) to switch back. 🙂

Cheers

Tim…

Don’t forget the COPY command in SQL*Plus (and SQLcl)

One of the developers asked me to copy a small table from Live to Dev. In situations like this, my first thought is to use the SQL*Plus COPY command. By the way, this command is also available in SQLcl.

It’s super-easy and has been around forever. Provided you can live with the data type restrictions, it’s a lot less hassle than expdp/impdp, even with the NETWORK_LINK option.

As always, it’s in the documentation, but the SQL*Plus help text tells you how to use it, so you don’t even have to RTFM is you don’t want to. 🙂

SQL> help copy

COPY
----

Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE} destination_table
[(column, column, column, ...)] USING query

where database has the following syntax:
username[/password]@connect_identifier
SQL>

What I ended up doing was something like this.

conn scott@dev

-- Using a TNS alias.
copy from scott@live create emp_20151201 using select * from emp;

-- Or using the EZ connect syntax.
copy from scott@my-host:1521/orcl create emp_20151201 using select * from emp;

Because it’s been around for so long, you rarely see people talk about it, which made me wonder how many newer folks may not have noticed it, hence this blog post. 🙂

Cheers

Tim…

PS. Here is the usage for the COPY command from SQLcl.

SQL> help copy
COPY
----

Non sqlplus additions are explicit BYTE options (default is CHAR
i.e. varchar2(1000 CHAR)) and LOB support.

Copies data from a query to a table in the same or another
database. COPY supports CHAR, DATE, LONG, NUMBER, LOB and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database}
{APPEND|CREATE|INSERT|REPLACE|APPEND_BYTE|CREATE_BYTE|REPLACE_BYTE}
destination_table
[(column, column, column, ...)] USING query

where database has the following syntax:
username[/password]@connect_identifier
SQL>