alter session NLS_LENGTH_SEMANTICS=CHAR inside the packages / stored procedures

Question

our application works on oracle 10g database and we are planning to migrate it to exadata now. For that sake we have to follow some compliance which would accepted by exadata. One of which is adding the statement to each and every existing package/procedure / function at beginning of the definition

alter session set NLS_LENGTH_SEMANTICS='CHAR'

I just want to check, may altering the session to change this session variable impact the code functionality? what are things we have to keep in mind while adding this statement to all the the objects?

Any leads would be highly appreciated

Answer 1

REVISION:

I am revising my answer because of recommendations from another respected Oracle employee expert (Sergiusz Wolicki), and the fact that the documentation was revised on this parameter to warn against setting it to CHAR as an initialization parameter, and the fact that the warning is still there for 12.1

http://docs.oracle.com/database/121/NLSPG/ch3globenv.htm#NLSPG234

Caution: Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in run-time errors, including buffer overflows.

CAVEAT: IF the DDL script is written without explicit semantics, then setting the parameter doesn't impact it, however, it apparently isn't something that was safely updated across the board within the Oracle product scripts.

With well-written code, it certainly wont impact the "code functionality", it is a setting that simply impacts new field widths. The crux here seems to be how comfortable you are that you can guarantee this.

However, if Oracle warns against it, they have experience and evidence for doing so. A legacy app or tool that isn't aware of length semantics may be impacted.

The Oracle default is traditionally BYTE because of backwards compatibility, but for a new database with no legacy stuff, there is no risk to changing it, and it doesn't impact the internal data dictionary because those are locked to BYTE semantics regardless of the database setting for NLS_LENGTH_SEMANTICS.