In this video, you'll learn 20 different tips for improving the way you use SQL Developer.
I've been using SQL Developer for years and have found a range of ways to improve how I use it.
My favourite tip is a recent addition to
SQL Developer and makes it way easier to export data in different formats.
So, let's get into the tips.
You're likely working with a few different files or tabs open at a time in SQL Developer.
When you want to create a new tab, to start a new query for example,
you'll need to select New, then select the type of item you want to create, then select
a folder to save the file to, then enter a filename, and only then will you get a new tab.
All of this takes valuable time if you just want to have a new tab.
There is a quicker way though.
You can use the New SQL Worksheet command. This adds a new tab in your editor immediately.
You don't need to select the filename or location. You can do all of that later.
To do this, you can click on the New Worksheet button.
Or, you can press Alt + F10 on Windows, or Option + F10 on a Mac.
Opening a new tab is pretty fast once you know these commands.
The second tip is called Block Selection.
A great feature of many IDEs is the ability to use multiple cursors. This allows you to make a single
change and have it take place on many lines, such as deleting characters or adding spaces.
This is handy when making changes to larger queries.
In this example, the book table has an alias of "b",
and we want to add "b." to the start of each column.
You can do this in SQL Developer with the keyboard or mouse.
First, we have to enable it. Go to the Edit menu and click on Block Selection.
Then, click in your code where you want the first cursor to go.
Then, press Shift + Down Arrow to extend the cursor to the next line.
Keep pressing Shift + Down Arrow until you have a cursor on all lines.
Then enter your text, such as "b."
You've now added the same text to every line with a few keystrokes!
You could also use this for entering a common value when you're inserting multiple rows,
or changing columns or functions on multiple rows, or add or remove spaces.
You can avoid having to copy this to a text editor or Excel to make the changes.
To return to the default editing mode, go to the Edit menu and disable Block Selection.
Tip number 3 is about exporting data.
Have you ever ran a query and needed to copy the results to another program or file?
If so, then Output Hints could save you time.
One way to get data into a CSV file, for example,
is to run a Select query, copy the results into Excel, then save as a CSV file.
A quicker way to do this is to use Output Hints. These are short comments you add to
your SQL code in SQL Developer that affect how the output is displayed.
Here's an example. We add the hint "csv" after the Select keyword.
It needs to be within these quotes and not have any spaces.
Then, run the query using the Run Script command, not the Run Statement command.
You'll see your output in CSV format.
You can do the same thing for JSON as well.
If you want to set it for all queries in your session, you can use SET SQLFORMAT CSV.
Then you can run your queries, and all output in your session will use this hint.
To reset it to the default, run SET SQLFORMAT.
This can make it easier to copy and paste your results into different formats.
Tip number 4 is to quickly format your code.
SQL Developer has a good built-in code formatter.
This can be useful if you're working with a large messy query
and want to clean it up, or if you want to use the same standards for formatting as your team.
You can format the code in the editor by selecting the code, then right-click, and select Format.
However, to save time, you can use a keyboard shortcut.
This is not set up by default, but you can enable it.
To set one, go to the Preferences window, and select Shortcut Keys in the left menu.
In the search bar at the top, under the heading, type "format".
There's an entry here in the Other category called Format. You can see the shortcut is empty.
Or, on Windows, it may be set to Ctrl + F7.
If it's empty, you can set it to the same combination of Ctrl + F7.
Click on the New Shortcut at the bottom of the screen,
and press Ctrl + F7 to set this as the shortcut. Then click Assign.
The Format command now has a shortcut.
Whenever you're working on SQL code,
you can simply press Ctrl + F7 and your code will be formatted.
The next tip will let you easily open the preferences window.
There are a lot of options you can change in the Preferences window.
Rather than expanding each section to find the right option, you can search for the right one.
You can also open the Preferences window using a keyboard shortcut.
On Windows it's Ctrl + , and on Mac it is Cmd + ,.
Press the keyboard shortcut, and the Preferences window opens.
Enter in the setting you want to change, and the list of options below is updated.
This is much faster than browsing the tree to look for the one you want.
Tip number 6 is about making it easier for you to view and edit code.
Many IDEs allow you to split your code editor window. This will allow you to
see two positions in the same file at once, or see two different files side by side.
SQL Developer also includes this functionality. Let's see how you can do it.
If you want to split the same window side-by-side, you can do that.
This is helpful if you have a long script and want to see two places of it at once.
To do this, right click on the tab name and select Split Vertically.
You can also select Split Horizontally, but I find Split Vertically easier to read.
You now have the same tab split in two windows. Any change you make to one side
is automatically visible on the other. You can also scroll them independently.
To return to a single window, right click on the tab header and select Unsplit.
If you want to see one file on the left and another file on the right,
you can create something called a Document Tab Group.
You can do this by either using the right click menu or by dragging the tab.
I prefer right clicking because dragging can be a bit fiddly.
To do this, right click on the tab heading you want to see on another side,
and select New Document Tab Group.
The tab you selected is now moved to the right.
The left and right sides are now independent and have their own toolbars and results panels.
This is helpful for viewing two files at once.
To return to a single window, right click on the tab and select Collapse Document Tab Group.
Tip number 7 is about working with tabs.
Switching between tabs can be done by clicking on the tab. But you can also do it with the keyboard.
To switch tabs, press Ctrl + Tab twice.
However, I've noticed it's a bit slower than other IDEs. So, to make it easier, press CTRL + Tab
twice, and hold the Ctrl key for a moment until a small menu appears. Press Tab until you get the
tab you want to show, and release the Ctrl key. The selected tab will then be shown.
The next tip is about code completion. A great feature of IDEs, including SQL Developer is
automatically completing code for you. This is often called Completion Insight or Intellisense.
You can trigger it in SQL Developer by pressing Ctrl + Space on Windows,
or Option + Command + C on Mac.
For example, we have this query here. The table has an alias.
To add the next column, we could browse the object tree to see what's needed.
Or, we can just press Ctrl + Space on Windows, or Option + Command + C
on Mac, to see what columns are available, and select the one we want.
This can be a real time saver when writing queries.
Whenever you run a Select query, the results are the query are shown,
and results of previous queries are removed.
If you want to keep the results of previous queries, you can pin the tabs manually.
However, if you want this to happen all the time, you can set SQL Developer to show any new query
results in a new tab. This will allow you to see the results of old queries and recent queries.
To do this, open Preferences. Navigate to Database then Worksheet.
Enable "Show query results in new tabs", which is near the bottom of the list.
This can save you time if you often refer to old results.
Tip number 10 is about adding some colour to your windows.
If you access different environments of your database, it can be easy to forget
which one you're in, or think you're in one environment but actually in another.
To avoid running code in the wrong environment,
you can set a colour for your connections in SQL Developer.
This means that a coloured border is shown around the window in SQL Developer,
and the connection name is coloured in the list.
To set a connection colour,
right click on the connection that you want to change, and select Properties.
In the Connection window, select a colour using the box on the top right of the window.
In this example, I've selected Red, because it's an account with DBA privileges.
This means whenever I connect using this connection, the border is shown in red.
I'll then think twice about any code I need to run.
How does this help you? You can set it to Green, for example, for any local or dev environments.
You could set it to Red for admin accounts or production databases.
You could set the colours based on the user that's logging in, if that's relevant for your system.
Tip number 11 is a quick one but often used.
You can use the keyboard to run an SQL statement,
which is quicker than clicking the Run Statement or Run Script button.
To run a statement, press Ctrl + Enter, or F9. Both of these work on Windows and Mac.
To run the script, which is the separate tab with the text output, press F5.
In most cases, I use Run Statement, so Ctrl + Enter is a command I use a lot.
To select the statement to run using Run Statement,
your cursor just needs to be inside the statement, or just after the closing semicolon.
You don't have to select it or move your cursor inside it.
The next tip is about enabling line numbers. Line numbers are a helpful feature in IDEs.
They can help you navigate to sections of code when you get an error message, so you know where
the error is coming from. They can also help you communicate with others about your code.
By default, they are turned off in SQL Developer. It's easy to enable them.
To do this, open the Preferences window, then expand Code Editor and go to Line Gutter.
Or, simply search for "line".
Check the box that says "Show line numbers". This will enable line numbers in your SQL code.
Tip number 13 is about seeing past statements.
You can return to a previously executed SQL statement in SQL developer pretty easily.
This is helpful if you make changes to your query and want to return to a query you ran earlier.
To do this, press Ctrl + Up on Windows, or Cmd + Up on Mac. Keep pressing these keys to scroll
through previous statements. You can use Down instead to go forward in the statement history.
When you run a Select query, you see the results in a tab at the bottom.
If you want to search for specific results, you often add a Where clause to the query.
But this means you'll have to run the query again.
In SQL Developer, you can filter results in the results tab without having to run the query.
This can save you time, especially if the query takes a while to run.
To do this, hover your mouse over the column header you want
to filter. A Filter icon appears on the right.
Click on the Filter button. A menu appears where you can select an existing value or enter a value.
Enter or select a value. You can do an exact match,
or any other kinds of match such as Less Than.
Press Enter, and the results are updated.
To remove a criteria, click on the Filter icon.
Then double click on the Remove in the grey section below.
The results will return to normal.
Now we're up to tip number 15, which is about making objects easier to view.
By default, when you click on an object in the Object Browser,
like a table, a new tab opens that describes the object.
For example, when you click on a table, a new tab opens on the right with details of that table.
This can be annoying if you're not used to it.
This feature can be disabled,
which means the information tab is only shown when you double-click on the object.
To do this, open the Preferences window and navigate to Database, then Object Viewer.
Deselect the option labelled "Open object on single click".
This means you can now click on an object and not see the information tab for it.
The next tip is about simplifying your view of objects.
When you expand a connection in the Connection Tree, you'll see a list of object types.
This can be a long list.
There's a good chance you're not using all of these types. Most of them just get in the way.
Fortunately, you can hide them using filters.
To do this, open the Preferences window and navigate to Database, then Navigation Filter.
Select the checkbox at the bottom labelled "Enable navigation tree filtering".
Now, deselect the objects that you don't want to show.
Click OK. Then, click Refresh on the connection panel.
These objects are no longer shown in the list.
You can hide much more than this if there are other object types you don't use,
which simplifies what you see and can save you time.
In the connection tree on the left, you have a list of object types for your connection. This
can be hard to work with if you have a long list, or if you only want to look at one type of object.
Another way to view objects is using something called the Schema Browser.
This may make it easier for you.
Right click on the connection and select Schema Browser.
The Schema Browser tab is then opened next to the Connections tab.
You can filter based on the schema and the object type.
The list shown below that is all of the objects in the database.
You can then open the properties of any of these objects, drag them to the main window,
right click, and do what you can do on the Connection Tree.
Tip 18, we're almost at the end.
If there are any settings that you change in your database session manually each time you login,
you can set them automatically using a script.
For example, turning on DBMS_Output, or changing the timezone.
SQL Developer allows you to write a series of commands in SQL,
save it as an SQL file, and specify that this file should be run whenever you connect to a database.
Write and save an SQL file, then set the file to run in Preferences,
then Database. Click Browse next to the box for "Filename for connection startup script".
Now, whenever you connect to a database, this SQL file will run.
Our second last tip, number 19, is about improving the startup time of SQL Developer.
When SQL Developer starts, it loads all of the components that are needed.
SQL Developer has a lot of great features, but many of them you won't use.
You can disable these features in SQL Developer which will make the application start up faster.
To do this, go to Tools, then Features.
The list shown is a list of all of the features in SQL Developer.
Deselect those that you aren't using, then click Close. The next time you start up SQL Developer,
it should take less time. You may be able to save several seconds off the startup time,
which is significant if you use this tool many times per day.
The final tip is about disabling the welcome page.
The welcome page is a helpful first page to see in SQL Developer,
but you may not want to see it every time you open SQL Developer.
To stop it from displaying whenever you open SQL Developer,
scroll to the bottom of the Welcome page and deselect "Show on startup".
You can then close the Welcome tab one final time.
This can save you a couple of seconds if you happen to close it anyway each time you open it.
And that brings us to the end of this video on tips for using SQL Developer.
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 one of these tips was the most helpful for you? Was it the ability to do block selection of
multiple lines? Or the ability to split your code into two windows side-by-side? Or something else?
Thanks for watching.