why is 'create table' in sql script execut

Question

This question already has an answer here:

• When do I need to use a semicolon vs a slash in Oracle SQL? 6 answers

I have an SQL script in which I want to automate the creation of a table. However, when I exec it, it seems as though it's trying to create the table 3 times. The first time, it creates the table. The next 2 times, it complains it already exists by throwing an

"ORA-00955: name is already used by an existing object"

Here my sql script(@/vagrant/scripts/db_tables_stubs.sql):

CREATE TABLE IDA_RADIUS_USER(
  CHECK_STRING    VARCHAR2(30),
  REPLY_STRING    VARCHAR2(300),
  RADIUS_USERNAME VARCHAR2(30),
  ACCOUNT_STATUS  VARCHAR2(30)
);
/
show errors;

And this is what I get:

SQL> select * from IDA_RADIUS_USER;

no rows selected

SQL> drop table IDA_RADIUS_USER;

Table dropped.

SQL> @/vagrant/scripts/db_tables_stubs.sql

Table created.

CREATE TABLE IDA_RADIUS_USER(
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


No errors.
CREATE TABLE IDA_RADIUS_USER(
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


No errors.

Commit complete.

All I want is to automate the process of creating that table.

Please help me. I can't figure out why that's happening. It's annoying!

Answer 1

You've said you have commented out code. It is those comments that are causing the problem.

SQL> create table t42(id number(38));

Table created.

SQL> /*insert into t42(id) values (1);*/
create table t42(id number(38))
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> /*exec dbms_stats.gather_schema_stats(user);*/
create table t42(id number(38))
             *
ERROR at line 1:
ORA-00955: name is already used by an existing object


SQL> show errors
No errors.
SQL> 

The slash (/) at the start of the comments is resubmitting the command in the buffer.

The SQL*Plus documentation also says:

Enter the SQL comment delimiters, /*...*/, on separate lines in your script, on the same line as a SQL command, or on a line in a PL/SQL block.

You must enter a space after the slash-asterisk (/*) beginning a comment.

So if you change your comments to have a space between the /* and the commented-out code that won't happen, and those will be ignored:

SQL> create table t42(id number(38));

Table created.

SQL> /* insert into t42(id) values (1); */
SQL> /* exec dbms_stats.gather_schema_stats(user); */
SQL> show errors
No errors.
SQL>