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.
8 Comments
I know that this is an old thread but how do I execute this statement in SQL Developer?
select XMLQUERY(‘declare function local:dummy($p_name as xs:string?)
as xs:string?
{
$p_name
};
{local:dummy(“y”)}’
RETURNING CONTENT) x FROM dual;
Note that XMLQUERY only accepts a string literal as an argument.
Never mind…I just realized that I just have to ensure that the semicolon isn’t the last character on the line:
SELECT XMLQUERY(‘declare function local:dummy($p_name as xs:string?) as xs:string?
{$p_name}; {local:dummy(“y”)}’
RETURNING CONTENT) x FROM dual;
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.
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.
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.
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.
Interesting, although I don’t know how we’d move it w/o canceling it. There’s no way that I know of to pass statements executing between sessions.
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.