ora-00972 identifier is too long oracle 10g

Question

I am getting the error mentioned in the title.

I am using a 36 charecter ID. This error is only thrown In my sqldatasource in my asp.net webform. It is not a problem when I perform updates in Oracle sql developer .

How can I fix this?

Biancabiancha

Answer 1

Oracle supports an identifier with at most 30 characters and your identifier length 36 is greater than that.so make it 30 character in length.

see this link

Beseem

I read that, but as I said I am able to update from Oracle SQL developer without getting that error? – Biancabiancha

Answer 2

If you are successfully executing DML (UPDATE tableName SET ...) from another environment such as SQL Developer, then your asp.net code must somehow be specifying or constructing the name of the table or a column name of the table incorrectly. Oracle will throw this exception if it parses either of these as >30 characters before any other checks.

As Srinivas pointed out, the maximum length is 30, so a 36 character identifier can't be working from SQL Developer - can you post the statement that is "working" in SQL Developer?

Daisey

Insert into SERVICE (SERVICE_ID) values ('03eb2491-9145-414f-8bdb-c5827a74151c'); and the field is SERVICE_ID NVARCHAR2(36) NOT NULL – Biancabiancha

I'm confused here - if you are getting the 00972 error, that refers to either the table name (SERVICE) or the column name (SERVICE_ID). An exception to the length of the DATA you're inserting will result in ORA-12899: value too large for column. Do you have some error trapping in your asp.net layer that might be mis-identifying the actual exception? – Daisey

Answer 3

SERVICE_ID NVARCHAR2(36) NOT NULL

Is your SQL Developer configured to use a multi-byte character set? Wild guess, especially as I would expect that to throw ORA-12899: value too large for column rather than ORA-00972.

Eichman

Answer 4

Ah, I see - it's not that column name that is 36 characters long, it's the actual data. Glad we cleared that one up.

As to "why is this happening from an ASP.NET SqlDataSource", I suspect that somewhere along the line there a problem converting between ASCII and Unicode. NVARCHAR2 is Unicode, but I suspect that something isn't playing nicely in the Unicode world. Check out you web server, your clients, your database, anything and everything you can think of - I suspect you'll find that somewhere along the line there's an ASCII client in there that doesn't talk Unicode properly, and is getting mixed up about the length of a Unicode string (32 Unicode characters take up 64 bytes (assuming UTF-16)).

Good luck.

Below