Oracle SQL Developer & VS Code Primer plus some Tips/Tricks

You’ve been using this for 5, 10, 15, or more years.

And then this joker from Oracle comes in and says, hey, you need to get used to this new platform for using SQL Developer, and it’s called, VS Code.

Too Long, Didn’t Read (TL/DR) summary of what’s happening with SQL Developer

• we’re transitioning from the Java Swing app version of SQL Developer…
• …to an extension in VS Code
• we’ll keep the old stuff around until we’ve moved all of the critical features over
• it’s free in either platform
• we’re doing the same with Data Modeler – work on that starts soon
• we’ll do annual updates for the old tools to address bug fixes, 24.3 coming SOON

In no particular order, some things that will help you get started

Getting Started: Installs and Updates

Install VS Code.

Open the extension marketplace, and search on ‘sql developer’ – install the extension that has our Logo on it.

You don’t need, nor do you need to worry about…

• Java – we sort it with the extension
• Oracle Client – everything is done via the JDBC driver
• TNSNames.ora – if you have one, we’ll use it, but not required

Getting Started: Migrating Connections

Export them from the old SQL Developer, to a .json file.

Import them using SQLcl (detailed step-by-step tutorial)

Once you’ve loaded them into SQLcl, they should appear in VS Code. If not, hit the ‘refresh’ button.

But “Hey!” you exclaim, what about our connection folders?

We’re working on that feature, should be ready later this Fall.

Where are my application files, the “SQL Developer settings?”

Well, the files that our extension creates and manages, like your connections, will be stored in one of two places based on your operating system.

Windows

Macs or Linux

Note that “.folder” directories are hidden on NIX and Windows doesn’t show AppData by default in explorer.

I can tell by counting the files in my history folder how many SQL queries/scripts I have stored in my extension.

Yeah, these are the folders you want to backup, or copy/archive and move across when you get a new machine.

General VS Code Things

VS Code has a couple of power features, or some might refer them to tricks if they had never sat down and read the docs.

Command Palette

ctrl or cmd+shift+p, OR right-click in an editor

Search on ‘sql dev’ to find all the extension commands you can invoke from the keyboard, or search on ‘color’ to do fun things like change the theme.

Zoom In and Out

You might be used to this from your terminal/cmd windows, but you can simply ctrl or cmd + or – to zoom the UI in or out.

Integrated Terminal

Need to do something, via the command line? Well you don’t have to leave VS Code to do it – they give you a terminal, and it’s a nice one.

Using SQLcl in VS Code tutorial.

Extension Settings

You can navigate to these from the application menu, or via the command palette. Either way, look for the Extensions area, and then SQL Developer.

Finding Stuff

Toolbars/Buttons

Upper right corner.

Database Connection Selector

Lower right corner.

Click the name to get the list of your connections, if you want to change it. You can always right-click on a connection and ask for a new SQL worksheet if you want.

Code Outline

This one’s a BIT tricksy. The SQLDev extension doesn’t have it’s own code outline, but our extension tells VS Code about our code to make it’s native code outline work for SQL and PL/SQL.

You’ll access it from here –

Working with SQL

A lot of the keyboard shortcuts are the same. Which by the way you can find this way:

• command palette
• type keyboard
• then search on sqldev

Code completion (in-depth tutorial)

Start typing. You’ll see pretty quickly suggestions that either come from our Code Snippets, our parser knowing the next keyword, or advice from the dictionary – names of tables, columns, procedures, etc.

VS Code LOVES to favor snippets over everything else. You can knock those suggestions down to the bottom, or even change them.

If you’re not seeing data dictionary advice, use the old SQLDev keyboard shortcut for help, Ctrl+Spacebar.

Browsing the database

• In-depth tutorial: navigating your schema
• In-depth tutorial: open declaration vs peek

Click on an object on the left, in the connection navigator to open a database object.

💡 TIP: Don’t like the ‘single-click’ open behavior? You can disable that in VS Code! Instead you’ll get a double-click to open database objects (warning: will also apply to files!)

One of the nice differences in VS Code are the breadcrumbs….old SQLDev had them, at the bottom, but only for PL/SQL programs. Now, you get them for ALL objects – we have created a virtual file system over the database objects.

This means you get things like directory/breadcrumb navigation for things like tables…just like you do for your python files.

Clicking these breadcrumbs allow you to navigate between objects…

Or you can navigate into an object directly from your code. Simply right-click on a table reference in your SQL to get to it…

That’s enough for now…

Writing or literally hacking this post together has me thinking. It’s TIME for another YouTube tutorial.

I’ll be adding to this post I’m sure. Feel free to add your questions and comments below to help me and others out, too!