SQL Developer: Why Do You Require Semicolons When Executing SQL in the Worksheet?

thatjeffsmith SQL Developer 6 Comments

Tell Others About This Story:

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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Comments 6

  1. Of course if you are using PL/SQL in the worksheet, you need to use
    /
    on its own line as a terminator. And you can do that for SQL statements too.
    But I’m old fashioned (or maybe just old), and switch to SQL*Plus whenever I run a PL/SQL script.

    1. thatjeffsmith Post
      Author

      Did you know you can also change the delimiter in SQL*Plus? For some reason when I see the ‘/’ it turns me off, reminds me of the GO in SQL Server for some reason. And that’s weird b/c I was using SQL*Plus and / WAAAAAY before I ever used SQL Server.

      1. Yes. It is an amazing way to annoy a DBA 🙂
        Just write an SQL deployment script that either doesn’t work when he runs it (because it relies on stuff in your login.sql), or redefines all the default settings, so that nothing works AFTER he runs it.

  2. I like the fact that you can Highlight a whole range of SQL statements (all properly terminated with a semicolon except for the last) and hit CTRL-ENTER, F9 or click the Run Statement button and all of the highlighted code will be executed sequentially building up a whole array of result tabs.

    Now if only I could demote the currently running query in the shared connection to an unshared connection without canceling it and restarting it in a new unshared connection life would be good.

    1. thatjeffsmith Post
      Author
      1. I was thinking more along the lines of renaming the current session but otherwise leaving the current session alone and opening a new session as the shared session. This way long running processes that started in the shared session could be moved to the background and the newly opened shared session wouldn’t be blocked by the previously shared session when working with the DB Objects tree.

        See this Feature Request: https://apex.oracle.com/pls/apex/f?p=43135:7:0::NO:RP,7:P7_ID:30662

        The request as worded kind of changes the concept of an unshared connection such that any connection could potentially be shared, but only one connection would be the primary connection used when querying the data dictionary.

Leave a Reply

Your email address will not be published. Required fields are marked *