Recently I was sent a PL/SQL script that an engineer has been working on and as soon as I tried to compile it at the client site, I received the 'PLS-00114 identifier too long' error. Sure enough, the identifier that Oracle was complaining about was longer than 30 characters. I thought that was a bit strange, how could this work for the engineer and not for me - of course I then realised that we had different database versions.


Long identifiers have been introduced in Oracle 12.2 and can now be up to 128 bytes (not characters!) In my case, since the client was not in a position to upgrade to the latest version of the database at the time, the solution was simply to shorten the names of the PL/SQL procedures. That was simple to do of course, but with proper testing the issue should not have arisen the first place.

So how do you make sure that even if you're running the latest database version in the development environment that your scripts will work on a lower target version? Unfortunately long identifiers cannot be switched on/off on their own, but the COMPATIBLE initialization parameter can be used to set the overall compatibility level of the database i.e. you can make your latest version behave like an earlier version.

Since long identifiers are enabled once compatibility is set to "12.2.0", using a lower value like "12.1.0" will disable them...and presto you can be sure that your script will compile in an earlier database version without having to install an earlier version!

I personally would not recommend switching to long identifiers too quickly though, especially in a heterogeneous environment that has different Oracle database versions installed and talking to one another overs db links, or if you're having to support a product that is certified to work on the latest Oracle database as well as versions prior to 12.2.



-i