In this video, we'll look at some of the main features of Oracle SQL Developer.
If you want to follow along on your own computer, you'll need to have SQL Developer downloaded
and installed, and have access to a database.
I've got separate videos on how to do this if you haven't done so.
So what will we cover?
We'll look at the following features in SQL Developer:
Creating a new connection to the database.
Then, creating a new SQL file and editor tab to write SQL code.
Writing and running a query
Pinning the results of a query
Several other buttons on the toolbar
Configuring the tool
And finally, formatting your SQL query
There are many other features in SQL Developer, and I'll have separate videos on those
Let's get into the demo.
This is the Oracle SQL Developer application.
This demo is on a Mac, but it works the same on a Windows or Linux computer.
On the left of the window you'll see a panel that includes database connections.
It will also show the database objects once we connect to a database.
On the right is the main area of the editor.
This is where you enter SQL code and see the results, which we will look at soon.
First, let's create a connection.
I've got another video that goes into more detail on this, so we'll look at it briefly
here.
To create a new connection, click on the green plus icon here.
A new window is opened to create a new connection.
In the panel on the left of this window is a list of existing connections.
In the main part of this window are a range of input fields to enter the information for
your connection.
Enter a name for your connection, which allows you to identify it in the list.
We'll enter Local Oracle Demo for now.
Enter a username and password.
This will depend on which database you're connecting to and which version.
Setting that up is outside the scope of this video but I've got other videos that show
you how to do this.
For this example, we'll enter a System username and a password.
Enter the rest of the connection details for the database, such as the server and SID.
These will also depend on your database setup.
Once you've entered the connection details, you can click on the Test button.
This will simulate a connection to the database and is a great way to check if the details
you have entered are correct.
Click on the Test button and you should see a Success message at the bottom.
If so, then the details are correct.
Click on the Save button to save the connection and click Connect to close the window and
connect to the database.
If you get an error here, which is likely if you're getting started with setting up
the connection, there are a few ways to solve it depending on the error.
You may get an error about incorrect username or password, or unknown SID, or network adapter
not found.
I've linked to a page in the description below on how to resolve common connection issues,
so take a look at that if you are getting an error here.
Once you have connected to the database, your SQL Developer window will look like this.
You'll see a new editor tab open here, with the name of the database connection at the
top.
In the top right, there is a drop-down that shows the current connection for this tab,
and you can change this to another connection if you need to.
We can run a query now in this tab here, but let's look at creating new tabs in the editor
first.
There are two ways to create a new tab.
The first is a simple tab, and the second is a new file.
To create a new tab, click on this New Tab button here.
This adds a new tab with a separate editor, allowing you to run separate queries and see
separate results, on the same connection or a different connection.
You'll need to select the connection from the drop-down on the top right of the screen.
The other way is to create a new file.
Click on the New File icon here, and you'll see a window pop up asking for some information.
You can leave the defaults and click OK, but this will create a generic SQL file somewhere
on your computer.
I prefer to specify a filename and select a location.
You can change this later, but specifying this at the start makes it easy to save the
file as you go.
Once you've done this, click OK and you'll have a new editor tab shown in SQL Developer.
The filename is shown at the top of the tab.
You'll also need to select the connection from the drop-down on the right before running
any code.
Now we've got an editor tab open, let's write some SQL.
You can enter SQL into this area here.
Enter it however you like: upper case, lower case, mixed case, on the same line or separate
lines.
SQL Developer includes a line number feature, which is turned off by default, but you can
change it in the Preferences which I'll show later.
There's also a Format SQL feature which we'll look at later as well.
You'll see some autocomplete features as well, depending on the objects you have in the database
and what query you're writing, which are helpful.
The font, size, and background colour can all be changed in the Preferences too.
So you've written the query.
Now let's run it.
There are two ways in SQL Developer to run a query.
The first is simply to run the query, and will show the output in the table.
You can do this by clicking the first green triangle here, which is the Run Statement.
A keyboard shortcut is available as well, which you can see if you hover over the icon.
Click the button and the results of the query are shown in a tab at the bottom of the window.
These results can be scrolled, sorted, and even copied and exported in different formats.
The other way to run a query is to click on the Run Script button.
This is the second button next to the Run Statement button, and is a green triangle
with a white rectangle behind it.
This will run all SQL in the file, which can be multiple statements.
The output is shown in a text output, not a table, similar to a command-line output.
Click this button and you'll see the output.
You can copy this output and paste it into a text file or anywhere else pretty easily.
With either method of running the query, once you run a query again, the results from the
previous query are overwritten.
You can keep the results visible by clicking the Pin Results icon, which is the red pin
on the results tab here.
This will save this tab of results on the screen and let you refer back to it.
Future queries will show their results in a new tab.
Another feature in SQL Developer is the ability to view an execution plan for a query.
To do this, you can simply click on the Explain Plan button here on the toolbar.
This will create and display the execution plan for the query.
Click this button, and you'll see the results here in a new tab at the bottom.
This is a simple query, so the plan isn't that long, but for more complex queries you'll
see larger plans.
This explain plan tab can also be pinned, which makes it helpful for referring back
to when making changes to your query.
Two other buttons on the toolbar are Commit and Rollback.
Commit is a feature in SQL that saves your data permanently to the database, and Rollback
is like an undo or "exit without saving" feature.
You can use the Commit or Rollback SQL commands in your editor, or you can click either of
these buttons.
Earlier we mentioned a Format SQL feature.
SQL Developer has the ability to automatically format your SQL to make your code consistent
and more readable.
To do this, right-click in the editor and select Format from the list that appears,
or use the keyboard shortcut that appears when you hover over it.
The code is then formatted according to the formatter rules.
If you don't like how the code is formatted, you can change a whole range of options for
the formatter in the Preferences section.
SQL Developer allows you to change many different options on how the software looks and behaves.
To find this, navigate to the Preferences option in the menu.
This could be in the Tools menu in Windows or in the Oracle SQL Developer menu on Mac.
The preferences window will then be displayed.
All of the different preferences are grouped in sections on the tree on the left, which
you can click on and expand and browse around.
There is a lot you can change here, and I'll have a separate video on recommended settings
in SQL Developer.
If you're looking for a specific setting, you can search using the search box here.
This will search across the section headings and each of the setting names.
For example, if you search for "line", you'll find the setting for enabling Line Numbers
in the SQL Editor.
And that brings us to the end of this tutorial on SQL Developer.
We've covered some of the major features, and of course, there is much more that can
be covered.
I'll create separate videos for these features.
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](http://databasestar.com/).
That’s where I share my best database-related content.
Which part of this SQL Developer tutorial was the most helpful for you?
Was it the ability to create new editor tabs?
Or the ability to pin your results to the screen?
Or something else?
Thanks for watching.