Visual Studio Code is a popular IDE for many kinds of development. If you want
to access your Oracle database to run SQL, you can use a separate tool such as SQL Developer.
But you don’t have to. You can run SQL statements on your database directly within VS Code,
saving you time from switching tools.
In this video, we’ll install the extension in VS Code to do this, connect to a database,
write and run some SQL, and see a few other features in the extension.
In order for you to connect to a database from VS Code, you’ll need to have VS
Code downloaded and installed. You’ll also need to have access to an Oracle database.
If you’re using a different database,
check out my other video here on using VS Code with SQL Server, MySQL, or PostgreSQL.
I assume you are using another tool to access the database,
such as SQL Developer, and want to use VS Code to access the database instead.
I’ve got separate videos on setting up a database if you want to watch those.
Let’s get into it.
Here we have VS Code. The first step to access a database from VS Code is to add an extension.
To do this, go to Extensions. You can click on this icon on the left sidebar,
or press Shift Command X on a Mac, or go to View, then Extensions.
The Extensions panel will be shown. In the search box at the top, type “oracle”.
Click on the extension that appears in the list, called Oracle Developer Tools for VS Code,
by Oracle Corporation. At the time of recording, it has 213,000 downloads.
You can see more information in the main panel here when you click on the extension. You’ll
see that it allows editing and execution of SQL and PL/SQL for Oracle databases.
On this page here for the extension, click on Install.
The extension will be installed in VS Code in a few minutes.
You may get this popup message here that says the extension requires
.NET Core Runtime. Click on the Go to Download page to get this.
Once the page loads, click on the link for your operating system at the top here to download
the installer. The version I’ve downloaded is about 50MB and takes a minute or so to download.
Once it’s downloaded, open the file. Follow the steps in the wizard to install it,
which is pretty quick.
When the installation is finished, you can return to VS Code.
You’ll see a cylinder database icon appear on the left sidebar.
In my VS Code there are two cylinder icons. That’s because I have another extension installed.
The cylinder icon for the Oracle extension is this one here with the multiple rings.
You can hover over it to see that it says Oracle.
Close the tab for the extension, and click on the cylinder icon.
In the Oracle Explorer section, there are several panels.
We’ll focus on the first panel, called Database.
Hover over the heading and you’ll see a couple of icons.
Click on the plus icon, and a new tab opens to create an Oracle connection.
We’ll enter the details to connect to your database. You’ll need to have access to an Oracle
database for this to work, which can be on another server you have access to, on Oracle Cloud, or on
your own computer. If you have issues connecting, I would suggest trying the connection in another
IDE such as SQL Developer, and if that doesn’t work, try Googling for the issue or error message.
I’m running an Oracle database using Docker, so I’ll fill out the details for that on this screen.
For the Connection Type, leave this as Basic.
For the database host name, enter localhost, assuming you’re running this on your own computer,
or enter the server name if it’s somewhere else.
For the port number, it should remain as 1521.
The service name is the important part, and the part that causes the most issues with Oracle
connections. Because I’m running an Oracle image on Docker, I’ll use a service name of
ORCLPDB1.localdomain. But your service name will likely be different, such as XEPDB1 or XE.
Leave the role as default if you have a regular user, or change it to SYSDBA if you’re connecting
as SYS. Enter a username and password that you want to connect with, which should match
a user that already exists on the database. I’ll connect as SYS so I’ll change the role to SYSDBA.
Check the Save Password box if you want the password to be stored and
not have to enter it every time you want to connect.
Enter a name for this connection, such as Oracle Local.
If you check Show More Options, you’ll see some options for the proxy and current schema,
but you shouldn’t need to enter those.
Once you’ve entered the details,
click Create Connection. A new entry in the Oracle Database panel on the left will be added.
Let’s connect to our database and start working with SQL.
To connect, you can click on the arrow to the left of the connection name,
or right-click on the connection and select Connect. It will expand
and you’ll see a range of folders that represent different objects.
If you’ve got an error here, it may be due to the connection details you’ve set up.
As mentioned earlier in the video, I would suggest trying to connect using a different IDE,
or trying to Google the issue you’re having if you get an error.
To make it a bit easier to see,
you can shrink the height of the other boxes. You can close them if you don’t want to see them,
but for this video, we’ll leave them open so we can see what may appear in them.
Now we’ve connected to the database, let’s create a table. We can do this by opening
an SQL editor window. Right-click on the connection name and select Open New SQL File.
A new tab appears at the top of the screen.
We’ll enter a Create Table statement to create a new table, called test_table, with one column.
Once the statement is written, we need to run it. One way to run the statement is to
right-click on the statement and select Execute SQL. The shortcut for this on a Mac is Ctrl E.
Once you select this, a new tab is opened with the results of your query. We’ve run a Create Table
statement here, which is shown, as well as the output of Table Created. So that was successful.
On the bottom left of the screen, we can see an entry in the History panel.
We can see a folder that matches our connection name. If we expand that, we can see the SQL
statement that we have run. This is helpful to see what statements we’ve run on our database.
In the Database panel, we can expand the Tables section to see a few categories:
relational tables, object tables, and XML tables. Expand the Relational Tables section.
I’ve logged in as SYS, which is a system account,
so this list may take a moment to load as there are a lot of tables that come with
this account. If you have your own account, your list may be smaller.
In the list of tables, we should see the table we have just created.
If you don’t, you can click on the Refresh icon here to refresh the list.
Let’s add some data to this table with an insert statement. Write a simple Insert
statement to add one row. You’ll notice here that the table name appears in the autocomplete list,
which makes it easier to write the query and get the correct objects.
Now, let’s run this query. If we right-click on the query and select Execute SQL,
it will run just this one statement. If we click on Execute All, it will run all statements in
the file. For this example, we only want to run the one statement, so click Execute SQL.
In the Results tab, you’ll see the SQL statement you ran and the output.
You’ll also see Commit Complete, which means the data has been committed to the database.
Now we have inserted a value, let’s see how we can select data from the table.
Write a select query to select all columns from the test table. To run just this query,
put your cursor inside it and press Control E, or right-click and select Execute SQL.
On the results tab, you’ll see the SQL you have run, as well as a table of the output.
There are a few handy features on the results tab here.
You can select or unselect all rows in the output,
which can then be copied or exported using the next few buttons.
You can save the results as either CSV or JSON data. The next buttons
allow you to save the selected rows in the output, or fetch all rows and save them.
Let’s see what else you can do with this extension.
If you find your table in the list on the left, you can right-click on it to see a menu.
Click on Show Data, and a new tab appears that shows all of the data in the table.
It’s similar to writing a Select * query on the table.
Right-click on the table and select Describe, and a new tab will appear that shows the output
of a describe statement. This shows the column names, whether they are null, and the data type.
If you right-click on the table and select Generate Select SQL,
a new tab opens that shows a Select statement on this table with a WHERE clause,
which you can edit and run. This is a great timesaver.
You can right-click on the table and select either Generate Insert SQL, Generate Delete SQL, or
Generate Create SQL, and you’ll see the relevant statement template appear in your editor.
You can edit the statement and run it.
The final feature we’ll look at is called bookmarks.
A bookmark is a saved query in your IDE that you can easily run in the future.
If you right-click on an SQL query in your editor, you can select Bookmark SQL.
A small window appears asking you to select an existing folder or enter a new folder name
if it does not exist already. Enter a folder name and press Enter.
Now enter a name for your query to bookmark, and press Enter.
In the Bookmarks panel on the left, the new folder appears, and within it is your query.
You can right-click on the query and either Run it immediately,
open it in an editor window, or copy it so you can paste it somewhere else.
You can also rename the query or delete it from the bookmarks.
This is a handy feature and will hopefully save you time with the kinds of queries you run often.
And that brings us to the end of this video on setting up VS Code to run SQL statements
on an Oracle database. As we can see, using this extension is pretty easy and allows
us to run SQL queries on the database without using a different application.
If you learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development,
visit databasestar.com. That’s where I share my best database-related content.
Which tip from this video was the most helpful?
Was it the process of installing the extension, or was it the bookmarks feature, or something else?
Thanks for watching.