Visual Code

Oracle Tools Is Great, Start ATP With A Click

Sometimes you’ve looked briefly at a product and when you return a couple of years later it is like opening a kinder egg. All sorts of new impressive things show up and you are left to wonder “are these old and I missed them or have they been added”.

Oracle Developer Tools extension for Visual Code is one such product. I’ve looked at it before and have always had it installed, but have not looked at what it can do beyond the basic things I used it for. I decided it was time to take another look and one of the first things I encountered was a great improvement. You can start and stop cloud databases with it now. Assuming they are on OCI. I know there are other cloud vendors, I just don’t know why.

I know there are other cloud vendors, I just don’t know why.

In this case it was that it cannot just set up a connection to a cloud database and execute SQL and PL/SQL, it has features for managing them via the APIs OCI has. Setting up with API-keys allows starting and stopping the database as well as a few other things. It also makes creating database connections to it easier.

Oracle has done a great job documenting how to do it, so I’ll later on refer to that and more focus on my experience setting it up and the couple of snags I ran into.

The basic work done is to generate API-keys for your user on the tenancy you have your database in. Then from that you paste config-definitions in one file. With that in place the extension can stop, start and terminate databases of the autonomous kind – ATP, ADW, JSON.

Let’s stop here and state one somewhat obvious thing. In order to use the extension you need to install it on your Visual Code. One even more obvious thing is that this requires you to use Visual Code. Anyway, in Visual Code go to the extensions and search for “Oracle Developer Tools”. Install it, depending on your setup you may need to also install .NET Framework. If you do, it will lead you to where to find it. If you are like me and rocking an M1 Mac, make sure you install the ARM version.

If you are on an M1 Mac, install the ARM version.

With the extension installed, you want to click on it’s icon on the left side.

Now there is a cog wheel in the “Oracle Cloud Infrastructure” section that is on the left side of the window.

Click that cog wheel and a web page Oracle has written with step by step will be loaded in your web browser. It is very detailed and worked perfectly so no reason to reproduce it here. If you have an autonomous database already, you only need to do step 2. I had two issues, the first was to load the page for my user account that step 2 starts with. I don’t know why, but it may be related to my account being the domain admin for the tenacy. To find my account’s page I instead navigated to “Identity and Security” – Users and clicked on my email address. Then the next issue I encountered was on the very end of step 2 where the config file is populated with data copied on the webpage. The path to the location of the PEM-file did not work when I had a tilde representing my home directory, so enter the absolute path there with no wildcards.

Once You have downloaded the PEM-file and set up the ~/.oci/config file just click the double-arrows and if all is done right, you will see your connection in that section. It should look something like this.

I have expanded the section for my tenancy (DEFAULT) as well as the section for ATP. In it you see the lone DB I have in it right now. In this case it shows the database is started. If it is stopped the star is red and if it is being stopped or started it is green

Right-click on the database and you get a contact menu allowing you to perform several actions.

Now you can start and stop your database and do other things directly from Visual Code. To me, the more things you can do without leaving Visual Code the more productive you will be.

It is worth noting that you can also create a new database, by right clicking on the heading of one of the three database categories (ATP, ADW, JSON). Pretty cool! It even allows you to create an “always free” DB directly from Visual Code.

The more things you can do without leaving Visual Code the more productive you will be.

There are of course a lot of other impressive things you can do with this extension. But that is a topic for another day.