Skip to main content

[DSP / Assemble] Oracle error ORA-00955

Caroline St.John
  • Updated

Assemble Only writes to Oracle Tables if UserID Owns the Table

In Assemble, users have been experiencing an ORA-00955 error when they try to write data to an Oracle table where the login used by DSP to connect to Oracle is not the owner of the table. This is a known limitation of Assemble.

This article contains the following:

Issue

The following error is displayed when a user writes data to an Oracle table that their UserID does not own:

CranPort could not create the target table. This may be due to the syntax of this ODBC data source. You can workaround this error by creating the target table manually, then executing the package. 
The service was unable to evaluate the non-query statement. 
ERROR [HY000] [Oracle][ODBC][Ora]ORA-00955: name is already used by an existing object 
at CranPort.Export.DatabaseTarget.StartTarget() 
at CranPort.Export.DatabaseTarget.WriteTargetImplementation(ICranPortReader reader) 
at CranPort.Export.Target.WriteTarget(ICranPortReader reader) 
at CranPort.Packages.Package.ExecuteItem()

What's Happening Behind the Scenes in Assemble?

This is what Assemble is doing during this process:

In order to determine that the Oracle table exists, Assemble runs two checks:

  1. SELECT * FROM USER_OBJECTS

Then if that isn't successful, Assemble will run:

  1. SELECT * FROM <table>
    NOTE: <table> is the name of the Oracle table without the Schema.

Even if the user has write permissions to the Oracle table, neither of those two checks will be true if the user is not an owner of the table in the Oracle database, so Assemble will attempt to create the table. That attempt is what then returns the ORA-00955 error.

NOTE: Assemble will attempt to determine that thetable exists regardless of whether or not the 
Target Table Exists checkbox is checked.

Workaround

Make the connection user the owner of the Oracle table that Assemble is attempting to write to. NOTE: this is not considered an Oracle best practice, but is required to workaround the known issue with Assemble.

To change the owner/schema for an Oracle table:

  1. Export the Table: 
    exp SYSTEM/<password> FILE=expdat.dmp TABLES=(<schema_name>.<table_name>)
  2. Import it to another User/Schema: 
    imp SYSTEM/<password> FROMUSER=<source_schema> TOUSER=<target_schema> FILE=expdat.dmp TABLES=(<table_name>)

Still Having Issues?

If you have changed the connection user to the table owner and you are still experiencing errors, your Cransoft services may be running in 32-bit instead of 64-bit. If you are still receiving the error after correcting that issue, please contact our support team at https://support.syniti.com.

Related articles