That is the question.
A recent post on the OTN forums asked about using semicolons and slashes as statement terminators. I dusted off an article I wrote for our development team over 4 years ago on this topic. This article received good reviews and is available on the OTN forums if desired. I thought I would also post it on my blog. Here is the article:
Slash or No Slash
by Brian Peasland
At our company, SQL scripts that are deployed are executed in Oracle’s SQL*Plus command-line utility, whereas many developers use a GUI tool like PL/SQL Developer or SQL Developer. The forward slash means something to SQL*Plus that is not needed in PL/SQL Developer or SQL Developer. As such, it can be confusing to know if you need to include a forward slash in your SQL scripts or not. Hopefully, this section will shed some light on what the forward slash does, when to use it, and when to not use it. Semi-colon Terminator For most SQL statements, the semi-colon is the statement terminator. For example, consider this simple SQL statement run in SQL*Plus:
SQL> select sysdate from dual;
SYSDATE
———
18-JUN-12
When SQL*Plus sees the semi-colon, it knows the end of the SQL statement has been reached and it can now execute the command.
SQL*Plus Buffer
You may not be aware that SQL*Plus has a buffer for its commands. If I press the ‘l’ key for ‘list’, then I can see the command currently in my session’s buffer.
SQL> l
1* select sysdate from dual
Not surprisingly, there is the command I just executed. I then executed another SQL statement and here is how my buffer now looks:
SQL> l
1 select sysdate,user
2* from dual
As you can see, I now have two lines in my session’s SQL*Plus buffer.
Slash = Execute Buffer
The first rule to understand about the forward slash is that to SQL*Plus, the forward slash means to execute the contents of the buffer. To illustrate this concept, I will execute a SQL statement, wait a few seconds, and then just execute that same SQL statement again but just executing the buffer.
SQL> select to_char(sysdate,’MM/DD/YYYY HH24:MI:SS’) from dual;
TO_CHAR(SYSDATE,’MM
——————-
06/18/2012 15:20:40
SQL> /
TO_CHAR(SYSDATE,’MM
——————-
06/18/2012 15:21:17
SQL> /
TO_CHAR(SYSDATE,’MM
——————-
06/18/2012 15:21:50
You can see that all I did the second and third time was to just type ‘/’ and hit enter and SQL*Plus executed the contents of its command buffer each time.
PL/SQL Blocks
The semi-colon statement terminator worked just fine by itself until Oracle introduced PL/SQL in Oracle version 7. The problem is that PL/SQL blocks can have multiple semi-colons to terminate the individual statements that make up that block. Consider this very simple PL/SQL block that does nothing:
SQL> begin
2 null;
3 null;
4 end;
5
Lines 2 and 3 contain perfectly valid statements that are each terminated with the semi-colon. And in line 4, we have the END keyword signifying the end of the PL/SQL block. If we were not allowed nested BEGIN/END pairs, then every time SQL*Plus sees “END;” it would know the end of the PL/SQL block has been reached, but we are allowed nested BEGIN/END pairs so the following is perfectly legal and valid:
SQL> begin
2 begin
3 null;
4 end;
5 null;
6 end;
7
You can tell from the above that just looking for “END;” is not enough because SQL*Plus would have tried to run the block after line 4. So how did Oracle decide to signify the that the PL/SQL block was ready to execute? The answer is by using the forward slash as you may already be aware. The second rule to understand is that all the forward slash is doing when you use it to end a PL/SQL block is to tell SQL*Plus to run what is in the buffer! This has not changed since before PL/SQL was created for Oracle 7. Consider the following sample:
SQL> begin
2 null;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> l
1 begin
2 null;
3* end;
On line 4, I typed the forward slash to execute the PL/SQL block. You can see that my block successfully completed. If we go back and look at the contents of my command buffer, you can see it contains everything but the forward slash. The forward slash is not part of the command buffer. So now, I’ll run a different PL/SQL block:
SQL> begin
2 dbms_output.put_line(‘Today is ‘||to_char(sysdate,’MM/DD/YYYY HH24:MI:SS’));
3 end;
4 /
Today is 06/18/2012 15:39:32
PL/SQL procedure successfully completed.
The forward slash said to SQL*Plus to run what is in its buffer, and the results are displayed. Now let’s type just the slash again and we should see our PL/SQL block get executed again.
SQL> /
Today is 06/18/2012 15:40:42
PL/SQL procedure successfully completed.
I did not have to type my PL/SQL block anew as it is currently in the command buffer.
PL/SQL and SQL Developer and PL/SQL Blocks
The biggest problem for most developers is that PL/SQL Developer and SQL Developer do not require you to use the forward slash. Why? Because you can hit Execute (F8) or Run Script (F5) to run your PL/SQL block. PL/SQL Developer knows that the moment you hit F8, you are intending to submit the PL/SQL block to be executed. In this case, F8 in PL/SQL Developer is doing the same job as the forward slash in SQL*Plus. Similarly, for F5 in SQL Developer.
The problem at my company is our team deploying code to production does not deploy code with PL/SQL Developer or SQL Developer. They use SQL*Plus because scripting multiple executions is easier with a command line tool. Many developers make the mistake of not including the forward slash for PL/SQL blocks in scripts because they do not need it, but if you want to deploy that code section in a SQL script, the forward slash is required at the end of each PL/SQL block.
When Not To Use Slash
So we’ve seen when and why we use the forward slash, but when is it bad to use it? The third rule to know is that it is bad to use the forward slash following a single SQL statement (not in a PL/SQL block), especially when that slash immediately follows a DML statement (INSERT, UPDATE, or DELETE). If my script contains the following:
select sysdate from dual;
/
Then I will get “double output” which is not what I normally intend to do in a script. I really only want one line returned, not two as the above script would do:
SQL> select sysdate from dual;
SYSDATE
———
18-JUN-12
SQL> /
SYSDATE
———
18-JUN-12
It is even worse when I use the forward slash following a DML statement because that statement will get executed twice. Consider the following script:
insert into test_tab values (10);
/
We now know that when I execute the two lines above in a script, SQL*Plus will execute it once due to the semi-colon statement terminator and then execute a second time because the forward slash tells SQL*Plus to run what is in the command buffer. When I execute the two-line script above, I get the following output:
SQL> insert into test_tab values (10);
1 row created.
SQL>
/
insert into test_tab values (10) *
ERROR at line 1: ORA-00001: unique constraint (PEASLAND.SYS_C00767176) violated
Oops! The first insert worked (1 row created.) but when the forward slash was entered, SQL*Plus tried to insert the same data and I got caught on a unique constraint violation.
Conclusion
Hopefully, this page shows why the forward slash is needed, what is does, and when not to use it. To recap:
- Include the forward slash at the end of each PL/SQL block
- Do not include the forward slash after any SQL statements not in a PL/SQL block.
- The forward slash after a single SQL statement will cause that SQL command to execute twice.
The tablet is taken viagra super active orally. If the person shows any allergy, he should stop why not look here generico cialis on line the usage. All these herbs are combined in right ratio to overcome from sexual weakness. lowest prices viagra People who are above the age of 65 and are having kidney problem or taking protease inhibitors, then it is recommended to levitra 20mg canada have these tablets an hour prior to the love making activity.
