Yesterday was an exciting day, we finally released our Oracle SQL Developer Extension for VS Code!
One of the first things you need to do to start using the extension is to create your first connection. And this immediately raises a very obvious questions.
Hey, I already defined these in SQL Developer, so…?
In an update coming soon, we’ll automatically import your existing connections from SQL Developer to the extension in VS Code, you won’t have to do anything. Believe me, I wanted this in v1, but I also wanted to ship this extension, and so we had to draw a line.
The good news is there’s a very easy workaround!
SQLcl makes this possible.
We’re going to use a ‘man in the middle,’ but in a good way! SQLcl and our Extension SHARE connections. That means, if you have a connection defined in SQLcl, it will appear in VS Code!
What does this mean, or what does it look like?
OK, so how do we get them into SQLcl?
Well, first you need to export them from SQL Developer (Classic).
I’m going to optionally include the passwords, protecting with yet another password, and then save to a file on my Desktop.
Now that we have the file, we can import them in SQLcl.
I’ve already shown how to do this in a post, here. But hey, let’s do it again.
I should test one of those, right quick?
Ok, now over to my VS Code instance with my SQL Developer Extension.
Just hit the “Refresh” button in your connections panel, and wait a second or 3…
Awesome, my connections are there! They work, and if open the properties, I can see they are what they’re supposed to be.
A few notes on connections
They’re stored securely, using Wallets
On Windows, look here –
On your Mac, they’ll be in your $HOME/.DBTools folder, so something like
/Users/thatjeffsmith/.DBTools/connectionsI have lots of connections, where are the folders??
For organizing many, many connections, this is very handy! And we agree, it’ll be coming later this year.
Same on being able to differentiate connections, by something LIKE coloring them as we did in SQL Developer Classic. Stay tuned.
What about Proxy or LDAP connections, I don’t see that connection type?
We’ll have formal GUI support for those also later this year, although it’s possible to create those connections RIGHT NOW.
For PROXY connections, just use the [user] syntax like Moritz shows here –
And for LDAP (and most any other type of connections, you can use the JDBC Url connection type) as a workaround.
You would use something like this, as shown in the docs –
jdbc:oracle:thin:@ldap://oid:5000/mydb1,cn=OracleContext,dc=myco,dc=com
But Jeff, all of this is great, but I don’t have SQLcl!!!
Yes, yes you do, you do if you have either SQL Developer OR the VS Code Extension. We ship SQLcl in both of those!
12 Comments
Man, nice post…. Thank you!
You’re very welcome! Have you had a chance to play with the new extension yet? Let us know when you have any feedback, good or bad!
Hi,
I’m trying to import the file using SQLcl 24.2 on Windows 10, however I am unable to access the file. The weird thing is that it says that the file was not found, however if I do cd and Tab, I see the file in the current dir’s list:
SQL> connmgr import -key mySecret ./conns.json
Import file ./conns.json not found in current directory or SQLPATH.
SQL> connmgr import -key mySecret conns.json
Import file conns.json not found in current directory or SQLPATH.
SQL> cd
SQLDeveloperIcons.icns hs_err_pid42188.log sdcli-Darwin.conf sqldeveloper-Darwin.conf
common.boot java11.conf sdcli.boot sqldeveloper-debug.conf
conns.json jdk.conf sdcli.conf sqldeveloper-nondebug.conf
history.log logging-debug.conf sdcli.exe sqldeveloper.boot
hs_err_pid15396.log logging.conf sdcli64.exe sqldeveloper.conf
hs_err_pid15704.log replay_pid15396.log splash.gif sqldeveloper.exe
hs_err_pid16696.log replay_pid28008.log splash.png sqldeveloper64.exe
hs_err_pid17888.log replay_pid3596.log sql sqldeveloper64W.exe
hs_err_pid28008.log replay_pid4064.log sql.exe sqldeveloperW.exe
hs_err_pid3596.log replay_pid42188.log sql.exe.bak version.properties
hs_err_pid4064.log sdcli sqldeveloper
Any idea how I can import the file?
just cd to the directory (using the cd command in sqlcl) and do the import with the file name, without the ./
I tried both versions (without and with “./”), as can be seen in my output above. Also I did the cd before, this is why cd and tab already shows the content of the directory. The file is there, but the command can’t find it.
I suppose there is an incompatibility between the version used to create the file and the one I’m using to import it.
The export was generated with the latest SQL Developer release 23.1.1 which comes with bundled SQLcl 23.1.
I tried to replace the bundled version with 24.2 and to re-export, but unfortunately it would not show my connections anymore, so I reverted back to the original files.
SQL> cd “C:\…\Documents\Programme\sqlcl\sqlcl\bin”
SQL> connmgr import -key mySecret conns.json
Import file conns.json not found in current directory or SQLPATH.
SQL> cd
conns.json dependencies.txt sql sql.exe version.txt
I’ve lost track here…can you open a SR with MOS or start a thread with start to finish steps on the Forums?
Hi,
for all those who never used sqlcl before and can not get to its command line (macos, but it should not metter):
[milan@PC1 milan % cd /Users/milan/sqlcl/bin/
[milan@PC1 bin % ./sql /NOLOG
SQLcl: Release 24.2 Production on Mon Sep 09 08:07:24 2024
Copyright (c) 1982, 2024, Oracle. All rights reserved.
[SQL>
Hello Jeff,
thank you for sharing the steps. Unfortunately there is no info about SSL encrypted connections. On my linux instance theese are no longer working as I suspect the trusted certificates can’t be found. The docs don’t seem to mention the proper way to add trusted certificates for TCPS connections. Could you please help to clear this up?
Thank you!
Gordon
If you’re relying on SQLNet/Oracle Client’s to do this, you’ll need to switch that up using pure JDBC.
The two routes for that right now would be…for example using the the Autonomous wallet.zip’s to get a mTLS connection…or look at the pure JDBC code path where you would get a TLS encrypted connection.
JDBD Driver docs/examples
https://docs.oracle.com/en-us/iaas/autonomous-database-serverless/doc/connect-jdbc-thin-wallet.html
Hello Jeff.
This saved me a lot of time, really really thank you!
However, after installing the extension I couldn’t open SQLc from VSCode. Even trying to open it from the terminal list, it opened a powershell instead.
It worked for me only after setting up manually 1 first working connection. Not a big deal, but if it’s not just me it could be added to the guide.
Thanks again,
Alessandro
Well…it could be just you? How were you trying to open it, exactly?
/Users/thatjeffsmith/.DBTools/connections
Should probably be
/Users/thatjeffsmith/.dbtools/connections
No .folder on my mac uses capitals.
Feature request, add a delete function for the connmgr or put it in VS Code.
Thanks
Mikael