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

Question

I am creating the table as below in the sql file, which will be called everytime there is a new file to process. So everytime a new file comes, I will drop this table and create again and load. When I am calling this from shell script, this table gets dropped and created successfully, but along with this i get an error as below. Any idea how to avoid this.

ERROR at line 1:
ORA-00955: name is already used by an existing object

Table Drop and create sql file :

DROP TABLE SCHEMA.TEMP_SOURCE;
CREATE TABLE SCHEMA.TEMP_SOURCE(
COL 1 VARCHAR2(30 CHAR),
COL 2 VARCHAR2(30 CHAR),
COL 3 VARCHAR2(30 CHAR),
);
/
EXIT;

Answer 1

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

That's because, you have put a slash / in the end of the script.

);

/

Due to which, the previous statement in the buffer is executed again. Which means, the CREATE TABLE statement is executed twice.

Remove the slash from the end. The semi-colon is enough as statement terminator for individual queries.

This is how I would do:

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'DROP TABLE TEMP_SOURCE';
  3  EXCEPTION
  4     WHEN OTHERS THEN
  5        IF SQLCODE != -942 THEN
  6           RAISE;
  7        END IF;
  8  END;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> CREATE TABLE TEMP_SOURCE
  2    (
  3      COL_1 VARCHAR2(30 CHAR),
  4      COL_2 VARCHAR2(30 CHAR),
  5      COL_3 VARCHAR2(30 CHAR)
  6    );

Table created.

SQL>

Having said that, you have multiple issues in the script.

COL 1 VARCHAR2(30 CHAR),

You cannot have a space in the column name. COL 1 is an invalid column name. You will get Invalid identifier error.

Another issue:

COL 3 VARCHAR2(30 CHAR),

);

There is an extra comma in the end of the column list.