Copying VS Code Developer Tools connections to SQL Developer

How to migrate your Oracle Developer Tools VS Code Extension connections over to the SQL Developer Extension – with a single bash command, and then some SQLcl connects!

You may have noticed the Developer Tools, sometimes known as ODT extension has been deprecated, and officially replaced with the SQL Developer Extension.

If you’ve been using the older extension, you probably have a bunch of connections you’ve created already. Recreating connections can be a pain, and so here’s a trick to make that much easier.

Step 1: Find your VS Code settings.json file

The ODT extension keeps it’s database connection information directly in the VS Code settings file(s).

On my Mac, that’s here –

/Users/thatjeffsmith/Library/Application Support/Code/User

And if we were to grep or more or cat the settings.json file, we’d start to find entries like this…

},
    "oracledevtools.connectionConfiguration.walletFileFolder": "/Users/thatjeffsmith/Oracle/network/admin",
    "oracledevtools.bookmarkFileFolder": "/Users/thatjeffsmith/Oracle/oracle.oracledevtools",
    "oracledevtools.download.otherFolder": "/Users/thatjeffsmith/downloads",
    "oracledevtools.defaultConnection": "JEFF HR",
    "oracledevtools.connections": [
        {
            "authenticationType": 1,
            "dBAPrivilege": "SYSDBA",
            "userID": "SYS",
            "passwordSaved": true,
            "dataSource": "pdb",
            "connectionType": 1,
            "tnsAdmin": "/Users/thatjeffsmith",
            "useConnectionCredsFromWalletFile": false,
            "name": "SYS",
            "color": "ff0000",
            "currentSchema": "",
...

If you found this file, you’re in the right place.

On Windows, it would be here:

Step 2: Use the jq command to parse and echo out a SQLcl connect string

Since SQLcl and the VS Code Extension share connection definitions, and also stored in JSON files, then we can write a simple bash script to create some commands we can copy and paste into SQLcl to get our connections created.

jq -r '."oracledevtools.connections"[] | "conn -save \""+ .name +"\" -savepwd "+  .userID + "/\"" + .password + "\"@"+.dataSource' settings.json

You may need to install jq on your machine, but it’s available for both Mac’s and Windows!

That output should look a little something like this –

Some notes:

• I am double quoting the connection names and passwords, that may or may not be necessary based on your connections
• The passwords may or may not be available depending on how you stored them in the ODT extension
• I haven’t accommodated the ‘as sysdba’ phrase necessary to handle the SYS connection – but that’s easy to add

Fire up SQLcl

But I don’t have SQLcl!! Yes, yes you do. Well, if you’ve installed the SQL Developer Extension in VS Code, you have.

Get to a SQL prompt…

sql /nolog

And then start going through those commands that we generated with jq:

As you run each connection request, SQLcl is creating the connection, but also creating the connection definition, and optionally saving the password.

If you’re using a TNS entry/alias, be sure SQLcl is setup to use the same TNS file that the ODT connection properties were reading from.

When you’ve ran through all of those, simply do a ‘connmgr list’ to see your list of connections. And if you need to add the SYS connection, you can just append the ‘as sysdba’ to the end of the connection request.

SQL> connmgr list
12 thousand tables ⚀⚁
23cVirtualBoxHR
ATP-ADMIN-🛠️ 🔴
AutonomousFunDemo🍻
F1 Duality View Demo 🏎️
HRREST
JEFF @ PDB2
JEFF HR
OE 🏬
SYS Container 🫙
SYS PDB
SYSTEM
SYSTEM @ MARIN
TNS example (HR)
hr 🟢
noname
SQL> conn -save "SYS" -savepwd SYS/"oracle"@pdb as sysdba
Name: SYS
Connect String: pdb
User: SYS
Password: ******
internal_logon: sysdba
Connected.
SQL> 

Before in ODT

After in SQL Developer

I’m guessing the more astute of you will be able to figure out how to take the
“color”: “ff0000” JSON value pair in the old connection definition and update the connection name for SQLDev to include UTF-8 characters… 🔴 (blog).

But Jeff, Why….?

We’ve decided that it makes sense for there to be only ONE extension offering for Oracle Database in Visual Studio Code, and that the strategic platform best suited for that is SQL Developer.

It’s SQL Developer that’s served the community for 20 years, offered features for developer, dba, data modelers, and business users alike, and so we made that decision.

If you want to see a bit more info the roadmap/SOD, here’s that on LinkedIn.

Yes there will be differences, and yes there will be a learning curve

But, we’re going to keep improving the products as always, and endeavor to give you what you need to do your work.

If you see missing features, let us know. We’ll use community feedback to help prioritize what gets added, when.

We still have much to add to the Extension before we’re officially ready to retire the Java desktop SQL Developer ‘Classic,’ so look for major updates every 3 MONTHS in VS Code and annual updates for the Desktop tools.