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

Report · May 23, 2023

Hi,

Trying to using the Oracle DB instead of sqlserver DB, and ran into below error.

ormSettings.dbcreate = "update";

With above configuration after calling ormreload() call, application keeps spinning forever. and getting below error.

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

when i change the above setting to "none",

ormSettings.dbcreate = " none ";

application throws table does not exist error, but in backend we have a table database.

when i change the same setting to

ormSettings.dbcreate = "dropcreate";

we are losing all data and tables got created newly. Application loading/running successfully.

By using “update” option, our application is working fine in MicrosoftSQLServer.

When we use the same configuration for oracle database, by changing the required configuration like dialect and more

We landed on this errors.

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

Using

Windows Server 2022 Standard

ColdFusion 2021 server Enterprise Trial version 10 days left

Oracle Database 21c Express Edition Release **** - Production

Any insight would be appreciated.

Report · May 23, 2023

My guess is that your Oracle example uses Oracle reserved words and your SQL Server example doesn't. Here's a list of Oracle reserved words. I didn't search for a 21c list of reserved words.

https://docs.oracle.com/cd/A97630_01/appdev.920/a42525/apb.htm

The best advice I can give here is, don't use reserved words for variables etc in your programs. The next-best advice I can give is to give them minor changes. For example, instead of "update" maybe use "update1".

Dave Watts, Eidolon LLC

Report · May 23, 2023

Hi Dave,

Thanks for your suggestions.

FYI, i am using below setting for reserved word as jvm arugemts.

-Dhibernate.globally_quoted_identifier=true

-Dhibernate.auto_quote_keyword=true

Report · May 24, 2023

Hi @khadar299107844wkv ,

That flag should not be -Dhibernate.globally_quoted_identifier=true. It should instead be (in plural form):

-Dhibernate.globally_quoted_identifiers=true

Furthermore, verify from a suitable Hibernate reference whether you need to add the additional flag,

-Dhibernate.globally_quoted_identifiers_skip_column_definitions=true

Report · May 26, 2023

Turns out I didn't have my glasses on when I read your post the first time. Or maybe I didn't have coffee. Anyway, there's nothing wrong with using "update" for the dbcreate value. @BKBK is providing better advice here. Sorry!

Dave Watts, Eidolon LLC

Report · May 23, 2023

Hi,

same issue with

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Report · May 27, 2023

Hi,

same issue with

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

By @khadar299107844wkv

Thanks for the update. I had missed it.

Report · May 25, 2023

After loading till request time out, application throws below error "Error executing DDL via JDBC Statement" and in hibernate sql log file i can see below script running which is wrong.

Hibernate: create table XYZ ( ******, ****** )

in short,

after calling ormreload() method server trying to create tables which is why i am getting "Error executing DDL via JDBC Statement" Error message.

Report · May 25, 2023

Did you apply the correct JVM flags as I suggested in my last post?
In your Application.cfc, what is the value of this.ormsettings.dialect ?

Report · May 25, 2023

There is something else. Looking back just now, I see the following in your original post:

ormSettings.dbcreate = " none ";

ormSettings.dbcreate = "dropcreate";

To be sure, these settings should be, respectively

this.ormSettings.dbcreate = " none ";
this.ormSettings.dbcreate = "dropcreate";

You should make corrections where necessary.

Report · May 25, 2023

@BKBK

yes it's exactly like that only.

this.ormSettings.dialect = "Oracle10g";

this.ormSettings.dbcreate = "update";

and added below in jvm args

-Dhibernate.globally_quoted_identifiers_skip_column_definitions=true

and removed existing one.

still the same issue.

Report · May 25, 2023

You removed the existing one? I think you need all three Hibernate JVM flags.

Report · May 25, 2023

removed one to test only a specific scenario,

getting same error while keeping 3 hibernate args.

Report · May 27, 2023

Hmm, I agree with you that this is weird. Dbcreate="update" should not try to recreate an existing table.

Write down the list of tables for the Oracle datasource. Are any of the table-names Oracle reserved words?

Let's assume the implementation of the 3 JVM flags is correct. As that doesn't work, then maybe the problem isn't caused by reserved keywords.

There were fixes to ColdFusion 2021 that involved the database. What is your latest ColdFusion 2021 update? Make sure you have a recent update. The updates are usually cumulative.

Report · May 27, 2023

More suggestions:.

You are using Oracle Database 21c. With that, we may be hitting our heads against a brick wall. Why?
The ColdFusion 2021 Support Matrix says that Oracle Database 19c is the latest version supported by ColdFusion 2021. Is it possible for you to use Oracle Database 19c?
Does it help when you do the following test?
- Create a CFM page called reloadORM.cfm. It contains the following line of code:
```

<cfset url.reloadORM=true>
```
- Add the following code to the onRequestEnd eventhandler in Application.cfc:
```
<cfif structKeyExists(url,"reloadORM") and url.reloadORM>
	<cfset ORMReload()>
</cfif>
```
- Launch the page reloadORM.cfm in the browser. Doing so will reload ORM.
- Check to see whether that solves the problem.

Report · May 27, 2023

Thanks for your suggestion.

I am getting this issue as I was doing the same as you mentioned above. And i am using CF2021 udpate 6

Tried with Oracle DB19c also you will find my update above on 23rd May 2023.

in short, the below are 3 scenarios out of them two are failed.

1.

this.ormSettings.dbcreate = "dropcreate";

by configuring dbcreate to dropcreate. CF Server working as expected. So my application ran successfully.

It’s dropping and recreating all tables though which have reserved keywords in it.

on ormreload() CF Server dropping all tables and recreating schema again. Which is expected, so this way i can't configure my application dbcreate to have “dropcreate”.

this.ormSettings.dbcreate = "none";

By step1 I have all schema and so i can run application with this configuration.

but here I am getting an error on table 'xyz' does not exist for one of my entity. which is weird.

And the table name is not an reserved word.

CF server should not throw error in this scenario, because in 1st flow I have executed application without error.

With this error I can’t run application.

3.

this.ormSettings.dbcreate = "update";

By using this configuration CF Server should update existing schema, rather than creating existing entities. In hibernate log file I can see the logs generated for table creation which is why server is not throwing error message.

“Error executing DDL via JDBC Statement”

Sample log updated on May 25th 2023.

Report · May 27, 2023

Thanks for the update.

One thing is unclear to me. What happens when you run the application uder the following combined conditions:

with Oracle 19c;
with this.ormSettings.dbcreate="update";
and
after doing the ORMReload trick.

Report · May 27, 2023

No change in server behavior it keeps on running till request time out with same error message.