Friday May 11, 2012

NLS_LENGTH_SEMANTICS - Why not in the init.ora?

.Few days ago at the Upgrade and Migration Workshop in Hamburg when I did talk about Unicode Migrations a customer had an interesting question.

"Why does the documentation states NOT to set NLS_LENGTH_SEMANTIC parameter to CHAR in the init.ora/spfile to enable char semantics for newly build objects by default?"

Honestly I was not aware of that hint. So I did some research and tried to get an answer from the developers.

The documentation says clearly not to set that parameter permanently:

  • Oracle Database Reference on NLS_LENGTH_SEMANTICS
    • 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 runtime errors, including buffer overflows.

  • Oracle Database Globalization Support Guide on NLS_LENGTH_SEMANTICS
    • Same warning as above - but also states:
    • NLS_LENGTH_SEMANTICS does not apply to tables created in the SYS schema. The data dictionary always uses byte semantics. Tables owned by SYS always use byte semantics if the length qualifier BYTE or CHAR is not specified in the table creation DDL.

And my colleague from Poland, Sergiusz Wolicki did reply (as always) very quickly (thanks!!!):

"The warning is general as the problem may affect Oracle data dictionary scripts for schemas such as SYSTEM, CTXSYS, ORDSYS, XDB, SYSMAN, Oracle application scripts for schemas such as APEX_030000, APPL, APPLSYS, GL, and other eBS or Peoplesoft or JDE, but also third party prepackaged applications and customer's own legacy stuff.

The recommendation is to avoid the dependency on the parameter altogether
. The character and byte length semantics should be explicitly specified in column definitions and PL/SQL code:


CREATE TABLE emp( ..., first_name VARCHAR2(100 CHAR), last_name VARCHAR2(100 CHAR) )
/
CREATE PROCEDURE read_emp IS
  v_first_name emp.first_name%TYPE;
  v_last_name emp.last_name%TYPE;
  v_full_name VARCHAR2(202 CHAR);
BEGIN
  ...
  SELECT first_name, last_name INTO v_first_name, v_last_name FROM emp WHERE ...;
  v_full_name := v_last_name || ', ' || v_first_name;
END;
/

If you find specifying the data type and the length explicitly as an obvious thing, you should also expect that the length semantics (length units) are specified explicitly.

If you need to migrate existing scripts quickly, put an ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR at the beginning of each script and after each CONNECT command in the script.
"

About

Mike Dietrich - Oracle Mike Dietrich
Senior Principal Technologist - Database Upgrade Development Group - Oracle Corporation

Based near Munich/Germany and spending plenty of time in airplanes to run either upgrade workshops or work onsite with reference customers. Acting as interlink between customers and the Upgrade Development.

Contact me either via XING or LinkedIn

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
2
3
4
5
6
9
10
12
13
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today
Slides Download Center
OOW Slides Download
Visitors since 17-OCT-2011
White Paper and Docs
Oracle Blogs
Workshops
Viewlets and Videos
This week on my Rega/iPod/CD
Workshop Map
Upgrade Reference Papers