Visual Studio Code Integration with Oracle Autonomous Database
Oracle Developer Tools for VS Code 21.3.0
In my last blog post, I gave a feature overview for Oracle Developer Tools for VS Code. It’s a free extension for Visual Studio Code that enables editing and execution of SQL and PL/SQL for Oracle Database and Oracle Autonomous Database.
We’ve just dropped a new release (21.3.0) with some pretty cool features driven by feature requests from our growing community on the Oracle forums. I hope you will join us over there on the forums and let us know what you think.
The headline feature in this 21.3.0 release is integration with Oracle Autonomous Database (ADB). Of course, you’ve always been able connect to your ADBs from our Oracle Database Explorer tree control, from SQL scripts, or from the apps you are coding. Now we have made it possible to view, manage and create ADBs in Visual Studio Code. And we’ve made it almost effortless to get your credentials files and establish a database connection.
Oracle Cloud Infrastructure Explorer Tree Control
When you launch Visual Studio Code with the Oracle Developer Tools for VS Code extension installed, you’ll immediately notice the new Oracle Cloud Infrastructure Explorer (OCI) tree control. It is in the same pane as our Oracle Database Explorer.
The first time you use it, you’ll need to follow this quickstart to do a quick setup before the OCI Explorer can connect to your ADB resources. (If you forget that quickstart link, don’t worry, there’s a gear/sprocket/settings icon right next to the tree control that will launch that quickstart in a browser for you).
This one time setup consists of:
- Creating a free Oracle Cloud account if you don’t already have one
- Downloading a private key file and a config file to your machine
That’s it! It should only take a few minutes and you only have to do this setup one time — afterwards OCI Explorer will always be connected when you open Visual Studio Code.
Changing the Compartment or Region
Once your profile is shown (usually named DEFAULT), you can right click on the profile name and use the menu item Change the Compartment or Region if needed, to find any already existing Autonomous Database resources.
In the dialog that opens, use the Select New Compartment tree control to find the compartment you want and/or the Select New Region dropdown list to change to a new region. Once you’ve got the correct Compartment and Region combination, you will see your ADB instances (if any) under the Autonomous Transaction Processing Databases, the Autonomous Data Warehouses, or the Autonomous JSON Database folders.
Taking a look at the database icons you may notice that they are color coded. Those database icons with a red ball in the lower right hand corner are stopped or terminated. Those with a yellow ball are in the process of starting or stopping. And those with no ball at all are up/available. You can get more information about the state of the database instance by hovering over the icon. You might also notice a green star icon on some of them. That’s an indicator that it’s an Always Free instance. Since you get a limited number of free instances, this helps remind you at a glance which ones are Always Free.
Creating a New Always Free Oracle Autonomous Database
No ADBs yet? No problem. Let’s create an Always Free Autonomous Transaction Processing Database instance. It don’t cost nuthin’!
Right click on the Autonomous Transaction Processing Databases folder and select Create New from the menu. In the dialog that opens, check the Always Free check box. Fill in the Database Name, Display Name and provide the Password then click the Create Autonomous Database button. You’ll immediately see the new ADB database icon marked with a yellow ball indicating that it is starting up. When the yellow ball disappears from that icon, the ADB is up and ready to use. (If you run into issues there’s a log file that is outputted to the Visual Studio Code Output pane.)
Connecting to and Exploring your ADB Schema
Now that you’ve got yourself an ADB instance, let’s connect to it so you can run scripts against it and explore it in the Oracle Database Explorer tree control.
All you need to do is to right click on the ADB icon representing your instance and choose Create Connection in Database Explorer.
This will initially open a Download Credentials Files dialog. After you press OK, it will download those credentials and open a database connection dialog with everything prepopulated except for the user name and password. Once you provide those and press Create Connection, you are off to the races! From Oracle Database Explorer you can explore the instance’s schema in the tree control, and you can right click on the connection name to open new or existing SQL files. See my previous blog or the Quickstart for more tips on what you can do.
Stopping, Starting, Terminating and More
Last but not least — you can right click on your ADB instance icon and select Stop, Start, or Terminate from the menu to control your resource usage. (Again, check the output pane for logs if you run into issues).
There’s also a Change Administrator Password menu item and Service Console which brings up the Oracle Cloud Service Console web interface. As always, as this blog post ages you may notice new features added to this menu that are not mentioned here!
Keep an eye on this blog for more Visual Studio Code tips and tricks coming soon.. in the meantime, please do join us over on the forums to ask a question or let us know what you think. See you there!
