Oracle SQLcl Part 6

Oracle SQLcl – Stop Emailing TNS Entries: CONNMGR Gets Export/Import in 26.1…..

I’m fully aware I stated in an earlier blog post that we had reached the final part of the SQLcl series. Turns out I can’t quite stick to that statement. SQLcl is so powerful, and Oracle keeps releasing new amazing features with every release. So, without further ado, I present a new feature found in SQLcl: the ability to export and import connections saved in the connection manager. This feature came together with the release of SQLcl version 26.1.

The connection manager is a tool built into SQLcl, and as the name suggests it helps us manage connections. The basics — adding a connection, dropping a connection, and so on — are out of scope here, since this post focuses on export/import.

The ability to export connections from SQLcl makes it much easier to onboard new resources into a project, both developers and DBAs. I can simply create a connection pool and export it, so that other team members can import the same set. Very helpful when we’re dealing with tons of databases.

CONNMGR – Connection Manager features

–  Export persistent SQLcl connections to a secured archive.
–  The exported connection archive is encrypted.
–  The exported connection archive can be password protected.
–  List the content of a connection file.
–  Import the connections from the secure archive.
–  Can overwrite existing connections with the same name.
–  Can strip passwords stored in the archive during import.

WORKING WITH EXPORT/IMPORT IN SQLcl:

Starting with the help section is always a good idea. The help for connection manager can be viewed running the below command.

SQL> help connmgr

_{/*
The help text in SQLcl has consistently been thorough and reliable in my experience, but the official documentation can also be a good source. For the full picture on CONNMGR in 26.1, head over to the Oracle docs: specifically the 26.1 change log}, _{Changes in Release 26.1 for Oracle SQLcl Which lists the new export/import capability:
*/}

We now want to create a connection file we can hand over to DBAs during the onboarding process. To list all current connections in SQLcl we can use the command below.

SQL> connmgr list

If we only have 3 database connections, everyone can add them pretty quickly. But let’s assume our team of DBAs manages 2000+ databases — it would take quite a while if every new DBA had to add them manually. Instead, we can export the connections into a password-protected archive using the command below. To do so, we first need to save a secret name and a secret value in our current SQLcl session.

SQL> secret set <secret name> <secret value>

_{Note that the password must be at least 8 characters long and contain a combination of alphabetic characters and numbers or special characters. The export command then looks like this:}

SQL> connmgr export -key mypwd -all-connections <connections_file>

We can verify the content of the archive by running the command below. This way we can make sure only connections that should be shared are in the archive.

SQL> connmgr import -key  mypwd -list <connections_file>

Now we have an encrypted and password-protected archive that can be used for onboarding. Below is a simple example of how to import the connections, without using any of the extra options available — such as -duplicates to control how name collisions are handled, -strip-passwords to drop stored passwords during import, or -folder to import into a specific folder rather than the root.

SQL> connmgr import -key mypwd <connections_file>

A quick note to wrap up: unlike the desktop SQL Developer export, which can produces a .json file, the SQLcl 26.1 CONNMGR EXPORT writes an encrypted archive instead. The good news is that CONNMGR IMPORT handles both formats — the legacy SQL Developer .json and the new encrypted archive — so existing connection exports from the desktop version migrate cleanly into SQLcl.


As a disclaimer: 
I haven’t run all of this against an actual 26.1 install —
all the above example commands are based on infromation from the SQLcl 26.1 change log:  Changes in Release 26.1 for Oracle SQLcl

I see this as a really interesting feature and will for sure give it a try when given the chance – but for now I’d also appreciate any input from anyone who has tried it more.