-2

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!

GeekyMiss
  • 11
  • 2
  • 9
  • 3
    What is the entire text of the file `@/vagrant/scripts/db_tables_stubs.sql` – Forklift Mar 29 '17 at 18:21
  • 6
    I imagine it is [followed by a `/` or two](http://stackoverflow.com/q/28209880/266304), possibly as part of a comment. – Alex Poole Mar 29 '17 at 18:26
  • The appearance of the `No errors.` messages proves you're not showing us the whole thing. How do you expect us to help you when you're hiding things? – APC Mar 29 '17 at 20:33
  • 1
    http://stackoverflow.com/a/10207695/330315 –  Mar 29 '17 at 21:18
  • The rest of the file is commented out and irrelevant to the error I m getting. The "no Errors" statements are because I did not comment the "/\n Show errors;" in the script. So you can assure that's all the file content :) – GeekyMiss Mar 30 '17 at 08:11
  • 1
    No we can't. It's the commented-out code that is causing the problem. – Alex Poole Mar 30 '17 at 08:41
  • Thank you a_horse_with_no_name and Alex! Thank you very much!! I was misusing the `/` in my script. – GeekyMiss Mar 30 '17 at 15:30

1 Answers1

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>
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • But I'm using single line comments, like '-- this is a comment' instead of the /* */ style – GeekyMiss Mar 30 '17 at 14:40
  • Why are you still refusing to put your actual script into your question? You commented out the rest of your file with single line comments, but not `show errors`? – Alex Poole Mar 30 '17 at 14:42
  • Sorry Alex, I'll edit my question and post the whole script now. I think I've found what the problem is. My bad! Thank you all so much for your help. You've pointed me in the right direction by mentioning the slashes. – GeekyMiss Mar 30 '17 at 15:16
  • @GeekyMiss - your edit explains the first failing `create`, but not the second one, or the second `no errors` message. ANd shows no commented out code, so I'm not convinced you've shown all the code still. Anyway, this now seems to be a duplicate of one of the question I and a_horse_with_no_name linked to yesterday. – Alex Poole Mar 30 '17 at 15:20
  • I've come to realise that the error was coming from the fact that I have the forward slash character in my script. I wasn't using the / in the right way. My PL/SQL skills aren't great, so I've been learning the hard way. I thought you put the / always before typing "show errors" (I inspired myself from a sql script I got from someone else and which had PL/SQL blocks. You use a / to terminate a PL/SQL block. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END, My scripts does not have PL/SQL blocks so there's really no reason to have the forward slash in it – GeekyMiss Mar 30 '17 at 15:23
  • Or to have show errors; that's for looking at compilation errors from *stored* PL/SQL blocks (creating procedures, functions, packages, triggers...). It wouldn't be useful for an anonymous PL/SQL block either, which is what you are describing in your previous comment. – Alex Poole Mar 30 '17 at 15:26
  • It's the foward salshes that I had in various places that was messing up my scripts. I had `/ show errors ` statements in various places and did not suspect they were the problem. The comments I had them in the following style `-- My comment ` so I doubt they'd be the problem. Hence why I didn't show the whole thing. – GeekyMiss Mar 30 '17 at 15:27
  • oh I see. Thank you for that info :) I got rid of them. Thanks a lot :) – GeekyMiss Mar 30 '17 at 17:10