Sharing SQL Developer Connections #JoelKallmanDay

2023-10-11

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.

New / Select Database Connection in SQL Developer

In this window, you can change the Name, Username and 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.

all-connections.json in VS Code (original)

This is a minified JSON. Let’s select Format Document from the context menu to make it human-readable.

all-connections.json in VS Code (formatted)

Ahh, much better.

Removing Unwanted `info` Objects

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 connections array:

jq command to remove unwanted connections

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.

all-connections.json filtered with jq-vscode

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 odb190-localhost:

jq CLI command to remove unwanted connections

jq 'del(.connections[] | select(.info.NAV_FOLDER!="odb190-localhost"))' \
    all-connections.json > odb190-localhost-connections.json

The file odb190-localhost-connections.json is ready to be shared and imported into other SQL Developer installations.

3. Conclusion

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.