This content has been marked as final. Show 1 reply
There is bug #10143226 open to fix the documentation according to my recommendations. The wrong recommendations are in Reference, SQL Reference, and Globalization Guide (at least). These recommendations were given when the character length semantics feature was introduced into Oracle SQL, before it became obvious that compatibility and clarity is more important than some pseudo-pros of using the initialization parameter.1 person found this helpful
The proposed new wording of the problematic paragraph from your post is:
The length semantics of character data type columns, user-defined type attributes, and PL/SQL variables can be specified explicitly in their definitions with the BYTE or CHAR qualifier. This method of specifying the length semantics is recommended as it properly documents the expected semantics in creation DDL statements and makes the statements independent of any execution environment.
If a column, user-defined type attribute or PL/SQL variable definition contains neither the BYTE nor the CHAR qualifier, the length semantics associated with the column, attribute, or variable is determined by the value of the session parameter NLS_LENGTH_SEMANTICS. If you create database objects with legacy scripts that are too large and complex to be updated to include explicit BYTE and/or CHAR qualifiers, execute an explicit ALTER SESSION SET NLS_LENGTH_SEMANTICS statement before running each of the scripts to assure that the scripts create objects in the expected semantics.
The NLS_LENGTH_SEMANTICS initialization parameter determines the default value of the NLS_LENGTH_SEMANTICS session parameter for new sessions. Its default value is BYTE. For the sake of compatibility with existing application installation procedures, which may have been written before character length semantics was introduced into Oracle SQL, Oracle recommends that you leave this initialization parameter undefined or you set it to BYTE. Otherwise, created columns may be larger than expected, causing applications to malfunction or, in some cases, cause buffer overflows.
As you see, I do recommend hardcoding the semantics into the DDL. If you find it reasonable that column data types and lengths are always specified in the DDL and not in some session parameters, you will understand that it is good practice to specify the semantics in the same way.