Oracle change semantics

Historically database columns which hold alphanumeric data have been defined using the number of bytes they store. This approach was fine as the number of bytes equated to the number of characters when using single-byte character sets. With the increasing use of multibyte character sets to support globalized databases comes the problem of bytes no longer equating to characters.

Suppose we had a requirement for a table with an id and description column, where the description must hold up to a maximum of 20 characters. We might consider the following definition.

SQL> CREATE TABLE tab1 ( 2 id NUMBER(10), 3 description VARCHAR2(20) 4 ); Table created. SQL> DESC tab1 Name Null? Type - - - ID NUMBER(10) DESCRIPTION VARCHAR2(20)

We then decide to make a multilingual version of our application and use the same table definition in a new instance with a multibyte character set. Everything works fine until we try of fill the column with 20 two-byte characters. All of a sudden the column is trying to store twice the data it was before and we have a problem.

Oracle9i has solved this problem with the introduction of character and byte length semantics. When defining an alphanumeric column it is now possible to specify the length in 3 different ways:

  • VARCHAR2(20) : Uses the default length semantics defined by the NLS_LENGTH_SEMANTICS parameter which defaults to BYTE.
  • VARCHAR2(20 BYTE) : Allows only the specified number of bytes to be stored in the column, regardless of how many characters this represents.
  • VARCHAR2(20 CHAR) : Allows the specified number of characters to be stored in the column regardless of the number of bytes this equates to.

If we now create a second table using character semantics we can see the difference when the table is described.

SQL> CREATE TABLE tab2 ( 2 id NUMBER(10), 3 description VARCHAR2(20 CHAR) 4 ); Table created. SQL> DESC tab2 Name Null? Type - - - ID NUMBER(10) DESCRIPTION VARCHAR2(20 CHAR)

The default character semantics of the database or session can be altered using the NLS_LENGTH_SEMANTICS parameter.


DEMDACO - Home Willow Tree Inquisitive Child
Home (DEMDACO - Home)
  • Nurtured by your loving care
  • Figurine is 3 tall
  • Artist Susan Lordi hand carves the original of each Willow Tree figurine from her art studio on Kansas City, Missouri
  • Willow Tree is an intimate line of figurative sculptures that speak in quiet ways to heal, comfort, protect and inspire
  • Arrives in a gift box, ready for gift giving with an enclosure card
Unit Bricks 72 Piece Mini Unit Bricks Architect Set - (Wood Building / Stacking Blocks - Toy for Kids & Children 3 Years & Up)
Toy (Unit Bricks)
  • 3-4 year olds are detail-oriented, capable of matching and grouping items by shape and color. Just starting to exercise their imaginations with pretend play, at...
  • 5-6 year olds are investigators, able to hypothesize about their surroundings and test theories in play situations. They take pride in the final product of their...
  • 7-8 year olds have a stronger sense of self and are more comfortable taking risks with hands-on experimentation. They can engage in complex problem solving and communicate...
  • Are you looking for an educational toy for your kids or even someone else s children to enjoy? Look no further, Unit Bricks makes an excellent gift for boys or...
  • A brick is the basic unit of most building foundations. It s also the basic unit of key learning foundations. Simple unit brick play empowers kids and ignites their...
Bodega on Madison iPhone 6 Ouija Board Case (TPU), for the Spiritually Inquisitive Individuals!
Wireless (Bodega on Madison)
  • To insure purchase of genuine products, please make sure to check out using Bodega on Madison s add to cart.
  • Designed, assembled, shipped from the USA (not from China) exclusively by Bodega on Madison.
  • Glove-like fit that firmly locks into place and minimizes collection of debris between mating surfaces.
  • Shock resistant rubber-like plastic polymer (NOT HARD PLASTIC) is smooth, yet flexible. Does not stretch or loses its shape after prolonged use. Does not cling to...
  • Allows full access to all exterior functional components
FSKcase? Inquisitive Octopus iPhone 6 Plus Shadow green Tough Phone Case
Wireless (FSKcase?)
  • Protect and style your Phone case with this Solid Protector case
  • New and High grade Hard Shell quality
  • Lightweight, semi-rigid protection
  • Smart design make access to all buttons be so easy to use
  • You can provide me the picture you like ,i will print it on the case .Above 800*800 PIX
beingTRUE being TRUE - Satin Lip Shine - Inquisitive
Beauty (beingTRUE)
  • beingTRUE
  • Satin Lip Shine
  • Modern color meets sophisticated shine.
  • Inquisitive
Related Posts