Igor Kromin |   Consultant. Coder. Blogger. Tinkerer. Gamer.

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.
PLS-00114: identifier too long

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.


Skip down to comments...
Hope you found this post useful...

...so please read on! I love writing articles that provide beneficial information, tips and examples to my readers. All information on my blog is provided free of charge and I encourage you to share it as you wish. There is a small favour I ask in return however - engage in comments below, provide feedback, and if you see mistakes let me know.

If you want to show additional support and help me pay for web hosting and domain name registration, donations, no matter how small, are always welcome!

Use of any information contained in this blog post/article is subject to this disclaimer.
comments powered by Disqus
Other posts you may like...