We have an external database that we have imported and are reading from without issue.  Now we are trying to do a write and are getting the ORA-00972 error indicating that the identifier is to long.


We are just using the create or update function from the entity that Outsystems generates in the extension.   Is there a way to see the SQL that Outsystems is generating when the save is attempted?   

Your DBAs on the external DB can do a profile/trace to see the executed SQL. Since the external DB is responsible for generating an ID, that would indicate that your DB and it's sequences, and the triggers are the problem (assuming the "identifier" you mean in the primary key) and that's also a task for your DBAs to solve. If it's not an autonumber ID, then you are probably putting the wrong data in there.

J.Ja

Justin James wrote:

Your DBAs on the external DB can do a profile/trace to see the executed SQL. Since the external DB is responsible for generating an ID, that would indicate that your DB and it's sequences, and the triggers are the problem (assuming the "identifier" you mean in the primary key) and that's also a task for your DBAs to solve. If it's not an autonumber ID, then you are probably putting the wrong data in there.

J.Ja


Here's the interesting thing... as a control test we are doing a Get of the record from the external in OS.  Then immediately doing an update using those same values, and it returns the ORA-00972 error.   So odd.   

I think this error is related to the name of a column, table or alias in the query.

If you are able to read from the table, but not write, please, check if the readings are bringing all attributes or just only a few. Possibly trying to read all fields from the table should cause the same problem, if that's the case.

But as Justin mentioned, the trace can show the query and then you can see who is the culprit.

Cheers.

Perhaps you can put the full error message in here?

J.Ja

Josh Herron wrote:

Here's the interesting thing... as a control test we are doing a Get of the record from the external in OS.  Then immediately doing an update using those same values, and it returns the ORA-00972 error.   So odd.   

Alias used in the writing would also cause this problem.


Josh, can you share the name of the table, and the name of the columns? ORA-00972 happens when you have an identifier bigger than 30 characters - but identifier in this context means the name of a table or column or alias. So it's helpful to understand what could be hitting that limit - is your table name too big? or do you have some column with a big name?

It could be that you have a column name with 29 characters, and if the platform is adding 2 characters as some sort of prefix, you would run into the limit...


As a last resort, you can use a SQL query, and write the UPDATE statement yourself. That way you will have full control on what identifiers you choose.

@leonardo,

I think the platform adds EN to the name of the entities (when they are OutSystems entities), but I don't think it does this when it is an external database?

Eduardo Jauch wrote:

I think the platform adds EN to the name of the entities (when they are OutSystems entities), but I don't think it does this when it is an external database?

I think it adds that prefix regardless of where the entity comes from. The prefix is there to make sure the name is not ambiguous (e.g. if you name your entity SELECT it would cause syntax errors if not prefixed).

leonardo.fernandes wrote:

Josh, can you share the name of the table, and the name of the columns? ORA-00972 happens when you have an identifier bigger than 30 characters - but identifier in this context means the name of a table or column or alias. So it's helpful to understand what could be hitting that limit - is your table name too big? or do you have some column with a big name?

It could be that you have a column name with 29 characters, and if the platform is adding 2 characters as some sort of prefix, you would run into the limit...


As a last resort, you can use a SQL query, and write the UPDATE statement yourself. That way you will have full control on what identifiers you choose.

There are a bunch of columns... but one of the column names is exactly 30 characters, so that appears to be the problem child.   We are only updating one column on the DB so the manual query is what we are looking into.


Thanks all for the direction, our DBA was having a hard time doing the trace so this was great information to have.

leonardo.fernandes wrote:

I think it adds that prefix regardless of where the entity comes from. The prefix is there to make sure the name is not ambiguous (e.g. if you name your entity SELECT it would cause syntax errors if not prefixed).

I don't think so...
The prefix is always the same "EN".
What I saw is that the platform limits the name of the table and adds 1, 2, 3, etc, if it causes clash.

But as the OP is saying it has a field of 30 characters, if the platform adds anything, it will cause the error.