Home » Posts tagged 'Slash (punctuation)'
Tag Archives: Slash (punctuation)
Oracle SQL*Plus – Forward slash contd… (PL/SQL)
My friend was running an old PL/SQL script. The original developer seemed to have used another tool to run it, which was more pardoning with syntax than SQL*Plus. This exposed some new behaviors around (or lack of) Forward slash.
The scripts were spooling into log files. For one of the scripts, he kept getting numbers!! Like,
201
202
203
It was puzzling, because we were not printing anything in the PL/SQL block except errors. After analyzing the code for a bit, realized the script was missing the (REQUIRED) Forward slash after the PL/SQL block!!!
Here is a simple example:
test1.sql
declare
i number;
begin
select 1 into i from dual;
end;
<EOF>
I just included <EOF> here to show that’s the end of file.
Run the above script on command line as,
sqlplus <login> @test.sql
Now, you will see number 9 printed. If you keep pressing enter now, number will start incrementing. Unless you know what’s going on here, you could just fill up the screen with numbers quickly, like so:
9
10
11
12
13
…
At this point, if you know the problem, you will just press a “/” and off it will go. Or you can do a CTRL-C to kill the script!!
The above behavior is because the script file was missing “/” at the end of PL/SQL block! So, next time you code your PL/SQL block, make sure to include that blessed “/” at the end of the block!
Oracle SQL*Plus – Forward Slash contd…
Recently while working with a coworker I saw trouble with using Forward slash, first hand. He was running some old scripts. The original developer probably ran these in some other tool (typically other tools are more pardoning than SQL*Plus.
One of the scripts had DDLs and had some code commented out. Incidentally, the original developer used C-Style comments (those in between /* … */). And inadvertently, he created a Forward slash problem!!!!
I am including a very simple script below to illustrate this:
create table t (c VARCHAR2(30));
/* drop table t;
*/
First line there is a DDL that creates a new table. There is the C-Style comment.
SQL> create table t (c VARCHAR2(30));
/* drop table t;
*/
create table t (c VARCHAR2(30))
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
No way!!!! How come?? Well, you guessed it! It’s because of a Forward slash the developer left in the script inadvertently. Remember C-Style comment starts with Forward slash?? Forward slash means "RUN" to SQL*Plus. It tried to execute the previous DDL when it saw /* on the second line, thus the Error!
Funnily, this doesn’t happen in a DML nor in a PL/SQL block. So, if your scripts have DDLs beware of Forward slashes in any shape or form!!!
Oracle: Commit and Forward Slash
Further to my post earlier about DDL and Forward Slash, I would like to explain Forward slash’s role (or lack thereof) in other scenarios. One such scenario people seem to get confused with is COMMIT. They seem to want to find some type of connection between the two. Let me say it upfront, there is no connection between COMMIT and “/”.
Forward Slash is simply a SQL Executor and it happens to show up in various situations. This combined with SQL*Plus’s own quirks seem to imply certain other functions for this character. Let me repeat: it’s just an executor – short for RUN command in SQL*Plus.
One reason it could be construed as connected with COMMIT is that it may often be the last character in a file. When you run SQL*Plus in batch mode (running a script in a file), it typically exits after the last line (on *nix. It doesn’t DoS/Windows). And the SQL*Plus’s own behavior is to COMMIT a transaction when exiting. (Why? See here for a nice discussion about this.) So, it’s the exit that committed not the “/”. But. you can see why some newcomer to Oracle who just inherited some scripts, may think that “/” (being the last statement) actually committed the transaction!!!
The other reason could be DDL. DDLs are implicitly committed. (Actually it commits before and after the DDL statement itself, so beware if you are mixing DMLs and DDLs).
Now, typically a DDL could be ended with “/”. For e.g.,
INSERT INTO department(100, 'HR');
CREATE TABLE employee(
emp_id NUMBER,
emp_name VARCHAR2(40)
/
In the above case, department table will have the entry, even if the CREATE TABLE below failed. In generla, any DML (like INSERT/UPDATE/DELETE) before a DDL, DML would have been committed, even if the DDL itself failed. Since “/” is the last statement here, one could think, “/” did it!!! Another strike against “/”.
So, trust me folks! “/” doesn’t do anything else, except sending a SQL to Oracle in SQL*Plus. It’s just a shortcut for Run Command. But, depending on the situation, it may look like it’s doing something more. In such cases, analyze the SQL and try to rearrange them. Try to keep DMLs separate from DDLs to avoid surprises. And of course, set some standards for punctuations and stick to it to avoid any undesired effects.
Oracle: DDL and Forward Slash
In this blog, I write about various technologies I come across. Only a few topics seem to get a lot of attention. One of them happens to be Forward slash in (Oracle) SQL. I’ve already posted about this here and here. When I look at the search terms that lead to my site, I sense a lack of understanding of it’s usage. A search through the web quickly reveals that forward slash has been a source of confusion even among some of the experienced Oracle SQL developers 1. Based on the search terms, I can see the newcomers are often getting confused by the SQLs left for them by their predecessors and are looking for a clear explanation. “/” is often mixed in various statements, such as DDL, COMMIT etc and this leads to confusion about the statement itself. I will try to explain the role of “/” (or lack thereof) in each different scenario in separate posts.
One of the search term that I get a lot is “DDL and Forward slash” or some variation of it. Forward slash has nothing to do with DDL per se. If you saw my other posts, it is just a statement executor (RUN command for both SQL and PL/SQL). Unfortunately, semi-colon doubles as statement terminator and executor for SQLs (poor choice of shortcut) in SQL*Plus. There lies the confusion. Thus, the CREATE TABLE or DROP VIEW statements can be terminated by a semi-colon as well as “/”.
For e.g.,
CREATE TABLE employee(
emp_id NUMBER,
emp_name VARCHAR2(40));
and
CREATE TABLE employee(
emp_id NUMBER,
emp_name VARCHAR2(40))
/
are identical as for SQL*Plus is concerned. But, as a matter of preference, people tend to use “/” in DDL.
But this is not the case in other DDL statements that involve PL/SQL:
CREATE OR REPLACE FUNCTION f_get_employee(a_emp_id NUMBER)
RETURN VARCHAR2
AS
v_emp_name VARCHAR2(40);
BEGIN
SELECT emp_name
INTO v_emp_name
FROM employee
WHERE emp_id = a_emp_id;
END;
/
Here “/” is a must, as the above statement is one PL/SQL statement (from CREATE to END;). Remember, PL/SQL block ends with END; and it has to be followed by “/” to be executed 2 (see notes below). Thus some DDL statements (those that create programming constructs like packages, procedures, triggers) require “/” at the end. This, I think, led the newcomers to believe there is a link between “/” and DDL!!! There is no connection! You cannot generalize the use of “/” for all DDLs. For e.g., if you tried to do this (semi-colon followed by “/”) in a CREATE TABLE statement, you will have unexpected behavior:
2 emp_id NUMBER,
3 emp_name VARCHAR2(40));
Table created.SQL> /
CREATE TABLE employee(
*
ERROR at line 1:
ORA-00955: name is already used by an existing objectSQL>
In this case, the semi-colon followed by slash is a bad idea.
In a nutshell, use only semi-colon or slash at the end of a SQL (DML or DDL) and semi-colon followed by slash at the end of a PL/SQL. See here for the difference between SQL and PL/SQL).
As a personal choice, I normally use “/” for all DDLs (SQL or PL/SQL) and semi-colon for all DMLs. You can have one DDL per file in which case, the “/” is the last character in the file. You can also have multiple DDLs in a file, each separated by “/”. Try not to mix DDLs and DMLs in the same file! This may lead to more confusion and errors!
References
http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands
http://docs.oracle.com/html/A90108_01/sqcmd.htm
Notes:
1. See here for e.g., https://forums.oracle.com/thread/1020117
2. When I say “executed”, I mean the PL/SQL block that creates the function/procedure is executed – meaning the program is compiled and stored in the database. (Hence the name stored programs.) The actual program execution is done by
SELECT <function name> FROM dual; OR EXEC <procedure name>
Oracle: Forward Slash in SQL*Plus – Take 2
Take 2?
I posted about Forward slash in Oracle SQL before. I even went back and updated it a few times based on the traffic. Believe it or not, I still get a lot of traffic to this blog site solely based on this one little topic!! So, I thought I would spend a little more time on this.
WordPress has a nice feature in the Stats page that shows the Search engine terms that drove the user to my page. This is what some enters in say, Google and clicks Search. Here are some sample terms on Slash:
sqlplus forward slash
sql forward slash
forward slash in sql
oracle forward slash
oracle sql forward slash
slash in sql
sql slash
…..
commit vs forward slash
oracle pl/sql semi-colon or forward slash
oracle slash before commit
oracle sql commit slash
forward slash in ddl file
and so many variations of these. One thing that caught my attention (in bold) was the confusion user(s) had with commit and Forward slash. They really don’t have anything in common, but I guess Oracle behavior in different scenarios could lead one to that. So, I thought of explaining these one more time.
Then Vendor said: Let there be a Database and a (client) Tool
Let’s start from the beginning. A database system is typically run on a remote server. To work with it, the vendors – like Oracle, Sybase etc- provide a client tool. Oracle has always come with SQL*Plus as a client tool. This is a simple command line tool, yet powerful. People use it for both interactive and batch use. Oracle also came up with “better” tools eventually, like iSQL*Plus, SQL Developer etc. Similarly, Sybase has Interactive SQL. Apart from these tools, there are plenty of tools available from outside vendors. Some prominent ones for Oracle are Toad, SQLTools, PLSQL Developer etc. The modern tools are typically GUI based and provide plenty of options for interactive usage. Irrespective of that, SQL*Plus is still going strong. Several DBAs prefer this tool to flashy new GUI tools, so whichever tool you use, you may want to make sure your SQL will run SQL*Plus.
Why those Punctuation?
In a GUI tool, you can type up a SQL and click on a button (or press a key), the tool will get the SQL executed (send it to Oracle server, get the results etc). If there are multiple SQLs, you just highlight one and click and go. Now, SQL*Plus is a command line tool. It’s text based and often used in both interactive and batch modes. You couldn’t really click a button or press a key to execute SQLs. Especially, if it’s running SQLs from a file. Then you need a marker to show the end of each SQL, right? That, my friend, is the semi-colon – the statement terminator. Sort of like the period at the end of an English statement. Semi-colon is mentioned as the statement terminator in the SQL Standards books also.
Unfortunately, SQL Standards came much later. Database vendors already came up with their way of telling their tool to execute the SQLs. For e.g., client tools for Sybase and Microsoft SQL Server use go command. That makes sense. Type in a SQL. Key in go and it goes! (SQL is sent to server to be executed etc). Oracle (SQL*Plus) had a similar command – RUN!! So, if you are running SQLs in a file, you would have a SQL followed by RUN and then the next SQL followed by RUN and so on. This is good. But then Oracle decided to give us the short cuts. Like Forward Slash (“/”) for RUN1. (Those early days, every byte counted. Why type 3 chars for RUN and why store so many of them in a file? Slash will do it for cheaper price!!).
1. Actually RUN = Load & Run and the shortcut for it is “R” while “/” is to just “Run” what’s already loaded
What does Forward Slash mean?
So, there you have it. Forward Slash executes a SQL before it. If it was left like that, things probably would have been simpler. Oracle also decided to support the statement terminator, semi-colon. (Sybase and other databases did not require this earlier). Now, you have a semi-colon that could get the SQL executed and a Slash that could do the same thing. They are not the same, but served the same purpose of indicating end of a SQL statement in SQL*Plus!! This started the trouble, especially when people started mixing them. Imagine what happens when you have a semi-colon and a forward slash?
Then came PL/SQL. Now, PL/SQL is a programming language in Oracle which can have several embedded SQLs, often separated by you guessed it – semi-colon – the statement terminators. Now you can have a bunch of PL/SQL block in a file, how do you distinguish each block? Don’t you need something like a PL/SQL statement terminator? Well, semi-colon is already taken. So, Oracle came up with the ingenious way of reusing a character that already had a purpose – you guessed it, the Forward Slash!!!!! It was meant to be “RUN” the SQL, right? Now, it also has the purpose to terminate PL/SQL. (It could also mean RUN the PL/SQL). That paved the way to all the troubles we face in mixing these up.
Semi-colons and Forward Slashes
That mixture combined with SQL*Plus’s own quirks made it worse. In SQL*Plus, you can type a (SQL like) text and end it with semi-colon, it will pass it on to the database. You type a SQL followed by slash (“/”), it will be sent to the server as well. What happens when you have a semi-colon and slash? It gets executed twice. What happens when you have 2 semicolons? You might get an error on the second semi-colon. This is because of the way SQL*Plus works. As soon as you run a SQL, SQL*Plus stores it away in a buffer. Next time you type “/”, it re-executes what’s already stored in the buffer. Type “/” again, it runs it again. Until you type up a new SQL. You can smell trouble, right? People run into trouble because they don’t understand the nuances or simply because they mistyped.
Remember, “/” is a command. So, it has to be on a line by itself. Semi-colon on the other hand, has to be the last character on a line. If there is anything else after that, even a comment (–) or another semi-colon will cause an error!!
Anything after semi-colon generates an error.
Double semi-colon does too.
DML, DDL etc
When I mentioned SQL above, I meant any type of SQL. SELECT, INSERT, UPDATE, DELETE, CREATE TABLE etc. First 4 deal with data in tables. SELECT is a query. INSERT, UPDATE, DELETE make up the DML. There are whole bunch of SQL statements like CREATE TABLE that actually define the structure of a database object (here TABLE). These are called Database Definition Language (DDL). What we saw above applies to both DMLs and DDLs. You can use semi-colon to end DML statements as well as DDL statements. You can use Forward Slash with DML and DDL statements.
To confuse you a bit, general convention in Oracle world is to use Forward Slash to end DDL statements. This would make sense if you look at the syntax for creating Stored Procedures, Functions etc. These are PL/SQL objects. Remember, I said PL/SQL needs to be ended in Forward Slash? Now that we decided to (have to) use Forward Slash for DDL for Stored programs, why not use it for all DDLs? So, there you have it. This is how we arrived at Semi-colon (“;”) to end DML statements and Forward Slash (“/”) for DDLs.
How did COMMIT enter this discussion about Forward Slash?
Phew! We got through that fine. Now, where does commit come into the picture and why confusion about it? Typically, Oracle has Auto-commit option turned off by default. You will have to issue an explicit COMMIT to commit a transaction. (This helps us to have bunch of related DMLs inside a transaction).
Here again there are some short cuts and quirks in the tool that made it more confusing. If you didn’t add an explicit COMMIT and simply exit SQL*Plus, the tool will issue the COMMIT for you! And the DDLs do not require explicit COMMIT, as a DDL always issues implicit commits (See here) before and after the actual DDL statement. So, then with DDL it’s automatically committed and DDLs typically end with Forward Slash (“/”). Now, I can imagine why the user searched for a connection between Commit and Slash. He/She is probably running a script that has “/” at the end. The user probably ran the script and when SQL*Plus exited, it probably committed. Are you seeing the connection now? Really, there is no connection between “/” and COMMIT. They just look to be related because of quirks and twists in the tool and the specs.
What was all the blabbering about?
In summary, Forward slash is like go in SQL Server, but use with caution, as it’s not only the RUN command, but could be indirectly construed as a statement terminator as well. Forward Slash (“/”) does not commit. SQL*Plus may silently COMMIT your transaction when you exit. But, always add COMMIT and ROLLBACK explicitly.
And Remember, all this is specifically applicable to SQL*Plus. But, since it has become a de facto standard for running SQLs from a file, you make sure to follow these standards to avoid surprises.
And that mumbo-jumbo applies only to Oracle SQL or PL/SQL. Forward slash doesn’t have this type of special meaning in other databases!!!!
Power to Build 