SQL Developer: Why Do You Require Semicolons When Executing SQL in the Worksheet?
- by thatjeffsmith
There are many database tools out there that support Oracle database. Oracle SQL Developer just happens to be the one that is produced and shipped by the same folks that bring you the database product.
Several other 3rd party tools out there allow you to have a collection of SQL statements in their editor and execute them without requiring a statement delimiter (usually a semicolon.)
Let’s look at a quick example:
select * from scott.emp
select * from hr.employees
delete from HR_COPY.BEER
where HR_COPY.BEER.STATE like '%West Virginia%
In some tools, you can simply place your cursor on say the 2nd statement and ask to execute that statement. The tool assumes that the blank line between it and the next statement, a DELETE, serves as a statement delimiter.
This is not bad in and of itself. However, it is very important to understand how your tools work. If you were to try the same trick by running the delete statement, it would empty my entire BEER table instead of just trimming out the breweries from my home state.
SQL Developer only executes what you ask it to execute
You can paste this same code into SQL Developer and run it without problems and without having to add semicolons to your statements.
Highlight what you want executed, and hit Ctrl-Enter
If you don’t highlight the text, here’s what you’ll see:
See the statement at the cursor vs what SQL Developer actually executed?
The parser looks for a query and keeps going until the statement is terminated with a semicolon – UNLESS it’s highlighted, then it assumes you only want to execute what is highlighted. In both cases you are being explicit with what is being sent to the database.
Again, there’s not necessarily a ‘right’ or ‘wrong’ debate here. What you need to be aware of is the differences and to learn new workflows if you are moving from other database tools to Oracle SQL Developer.
I say, when in doubt, back away from the tool, especially if you’re in production.
Oh, and to answer the original question…
Because we’re trying to emulate SQL*Plus behavior. You end statements in SQL*Plus with delimiters, and the default delimiter is a semicolon.
