1. The Problem
I created a Docker image and a container for the Oracle Database 19c (19.19) for Linux ARM. The container contains ORDS, APEX and various sample schemas. Finally, an Oracle database that runs pretty fast on my Apple Silicon machine. Then I built a cold database clone by creating a container on another machine using a copy of the original Docker volume. So far, so good.
In SQL Developer I created a folder named
odb190-localhost with 29 connections. Now I’d like to copy these SQL Developer connections to another machine. Easy, right? Export all connections to a JSON file and import it in the other SQL Developer instance. Yes, this works. But if you have several hundred connections like me, you get more than you want. The target installation might contain connection names with different properties. Therefore you do not want to import and overwrite existing connections and to identify and delete unwanted newly created connections after the import.
So, what can we do?
2. Some Possible Solutions
2.1. Export Only Wanted Connections
Exporting some chosen connections works technically. However, the export wizard does not support the concept of folders and presents a flat list of all connections. Selecting all connections in a folder is not feasible without sorting or filtering options in the UI.
Finding all entries ending on
odb190-localhost is no fun. It might work for a handful of connections, but not for 29.
2.2. Export Only One Template Connection
As an alternative, you can export just one connection and import it into the target SQL Developer installation. Use this connection as a kind of template. Click on the connection and select
Properties in the context menu.
In this window, you can change the
Password and press
Save. Repeat that process for all other connections. Either now or later when you need them.
Not really a satisfying solution either, right?
2.3. Export All Connections and Filter with VS Code
Exporting all connections is easy. Let’s look at the file in Visual Studio Code.
This is a minified JSON. Let’s select
Format Document from the context menu to make it human-readable.
Ahh, much better.
On line 11 we see the property
NAV_FOLDER. We are only interested in connections with the value
odb190-localhost. Is there a way to delete all unwanted entries? Yes, there is. By using jq, a command line utility to process JSON files. There is also an extension for VS Code named jq-vscode, which simplifies the development of a jq command.
Here’s the jq command to remove all unwanted
info objects from the
del( .connections | select(.info.NAV_FOLDER!="odb190-localhost") )
In VS Code you can open a split window via the command
JQ: Open a new file to exec jq. There you can enter the jq command.
In the lower part of the screen, you can see the result of the jq command. Copy the output and save it to a file to be imported into any SQL Developer installation.
2.4. Export All Connections and Filter with JQ CLI
This is basically the same solution as before. The only difference is that we use the jq command-line tool. jq is available on all major platforms (Linux, Windows, macOS). Download it from here or install it directly with your OS package installer (e.g. apt, yum, dnf, brew, …).
I exported all connections to a file named
all-connections.json from SQL Developer.
With the following command, I produce the file
odb190-localhost-connections.json that contains only the connections in the folder
jq 'del(.connections | select(.info.NAV_FOLDER!="odb190-localhost"))' \ all-connections.json > odb190-localhost-connections.json
odb190-localhost-connections.json is ready to be shared and imported into other SQL Developer installations.
There are other solutions, not covered in this blog post, to create a filtered connection export from SQL Developer. For example, writing a dedicated SQL Developer extension. Or filtering an export file with another tool. Or processing a JSON export file in the database.
However, IMO jq is an excellent tool to process JSON data in shell scripts. Perfect for automation. In this case, it makes sharing a subset of your SQL Developer connections easier. For any filter criteria, you want to apply.