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;
*/

In the above SQL, there is a DDL on the first line that creates a new table. Second line has, the C-Style comment. Developer essentially commented the Drop statement, with /*. Here is the output in SQL*Plus:

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 second line of the script. He only wanted to comment out the Drop Table Statement, but SQL*Plus took the Forward Slash ("/") as the RUN command. It tried to execute the previously executed statement (create table) again, when it saw /* on the second line, and thus the Error! Now, you wouldn't have seen this error if the Create Table statement was not terminated with semi-colon(;)!

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!!! Above error could be avoided, by always commenting with SQL style comments, starting with "--".