SQL Developer: Why Do You Require Semicolons When Executing SQL in the Worksheet?
Posted
by thatjeffsmith
on Oracle Blogs
See other posts from Oracle Blogs
or by thatjeffsmith
Published on Thu, 13 Dec 2012 18:54:39 +0000
Indexed on
2012/12/13
23:12 UTC
Read the original article
Hit count: 563
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.
If you don’t highlight the text, here’s what you’ll see:
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.
© Oracle Blogs or respective owner