CODEWITHSUNDEEP
javaoracle-databasejdbcsequencedrop-table
Csi
Csi

Reputation: 526

Oracle sequence created twice. ORA-00955: name is already used by an existing object

EDIT After your advices, the code and the errors. I got an error "ORA-00955: name is already used by an existing object" now. The sequences creation create this error each time a DataStoreManager constructor is called.

Here is the code now : 

public class DataStoreManager {


    private Connection        connection;
    private PreparedStatement lookForAccount;
    private PreparedStatement addAccount;
    private PreparedStatement updaterBalance;

    private PreparedStatement reachOperation;

public DataStoreManager(String url, String user, String password) throws DataStoreException, SQLException {
      try {
          connection = DriverManager.getConnection(url,user,password);
          connection.setAutoCommit(false);

          this.createDB();

          lookForAccount = connection.prepareStatement("SELECT * FROM accounts WHERE account_id = ?");
          addAccount     = connection.prepareStatement("INSERT INTO accounts (account_id, balance) VALUES (? , 0.0)");
          updaterBalance = connection.prepareStatement("UPDATE accounts SET balance = ? WHERE account_id = ?"); 

          reachOperation = connection.prepareStatement("SELECT * FROM operations WHERE account_id = ? AND (date BETWEEN ? AND ?)");



      } catch (SQLException error) {
            error.printStackTrace();
            throw error;
      }
    }

public void createDB() throws DataStoreException {

      try {
              Statement statmnt = connection.createStatement();

            statmnt.executeUpdate("DROP TABLE operations");
            statmnt.executeUpdate("DROP TABLE accounts");

             statmnt.executeUpdate("CREATE TABLE accounts ( account_id INTEGER, balance DOUBLE PRECISION)");
             statmnt.executeUpdate("CREATE TABLE operations ( operation_id INTEGER, account_id INTEGER, amount DOUBLE PRECISION, mydate DATE NOT NULL)");



              //implement primary Key constraint.
              statmnt.executeUpdate("ALTER TABLE accounts ADD CONSTRAINT accounts_PK PRIMARY KEY ( account_id )");

              //implement foreign Key constraint
              statmnt.executeUpdate("ALTER TABLE operations ADD CONSTRAINT accountID FOREIGN KEY ( account_id )"
                    + "REFERENCES accounts ( account_id )");

              // implement sequence for auto implement.
              statmnt.executeUpdate("CREATE SEQUENCE operationID MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10");
              statmnt.executeUpdate("CREATE SEQUENCE logID MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10");

              // implement balance always positive constraint
              statmnt.executeUpdate("ALTER TABLE accounts ADD CONSTRAINT balance_must_be_positive CHECK (balance >= 0)");

              // Auto operation.
              statmnt.executeUpdate("CREATE TRIGGER addingOrder BEFORE UPDATE ON accounts FOR EACH ROW BEGIN"
                    + " INSERT INTO operations(operation_id, account_id, amount, mydate) "
                    + " VALUES (operationID.nextval, :OLD.account_id, :NEW.amount - :OLD.amount, SYSDATE) END");


          connection.commit();

      } catch (Exception error) {
            error.printStackTrace();
      }
  }


  public boolean createAccount(int number) throws DataStoreException, SQLException {

      try {

         lookForAccount.setInt(1, number);
         Boolean result = lookForAccount.execute();

         if(result == true)
         {
                addAccount.setInt(1, number);
                addAccount.executeUpdate();
                connection.commit();
                return true;

         }
         else
         { return false;}

      } catch (SQLException error) {
            error.printStackTrace();
            throw error;
      }
  }

Here are my errors : 

   java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:145)
Running single-user tests...
Running multi-users tests...
java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:160)
user#0[services.DataStoreManager@e00c09]: starting
user#0[services.DataStoreManager@e00c09]: exiting
java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:160)
user#1[services.DataStoreManager@111f9b]: starting
user#1[services.DataStoreManager@111f9b]: exiting
java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)user#2[services.DataStoreManager@1363271]: starting
user#2[services.DataStoreManager@1363271]: exiting

    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:160)
java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:160)
user#3[services.DataStoreManager@114f313]: starting
user#3[services.DataStoreManager@114f313]: exiting
java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)user#4[services.DataStoreManager@d767dc]: starting
user#4[services.DataStoreManager@d767dc]: exiting

    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:160)

Upvotes: 0

Views: 2331

Answers (1)

Mark Leiber
Mark Leiber

Reputation: 3138

You will have problems in your createDB() method, if any of your SQL statements generate an error (for example if a table doesn't exist and you try to drop it). At that point your code will jump into the catch block and none of your database objects will be created. You need to wrap each DROP statement in a try/catch block so that any error can be caught and handled (or in your case it's probably safe to ignore) and then the CREATE statements will run.

You also are only running DROP statements for your tables. You need to do the same for your sequences and trigger.

Your trigger is also invalid. The accounts table only has columns for account_id and balance. You can't reference :old or :new values for the columns in the operations table. So :old.amount and :new.amount are invalid, and :old.account_id is the account_id from the accounts table (which turns out to be the same as the account_id in the operations table since it's a foreign key). You're also missing two semicolons. It should look like this:

statmnt.executeUpdate("CREATE TRIGGER addingOrder BEFORE UPDATE ON accounts FOR EACH ROW BEGIN"
                    + " INSERT INTO operations(... columns ...) "
                    + " VALUES (... values ...); END;");

The way you have things coded, you are trying to do some cleanup first by DROPPING everything, then you CREATE everything. When your test is done you will be left with all of your database objects in place. I would suggest that you reverse your order so that your create your objects when your test starts, and then do a tear down at the end in which you DROP everything so that your database stays clean.

Upvotes: 1

Related Questions