Despite the doubts about the topic "Query Transformations" (described in my previous post) presented at OOW which was quite technical I found extremely nice comments in different blog posts written by the attendees of my presentation. Thanks for sharing that with others. I was really happy and at same time surprised to have the room almost full and I hope everybody has learned at least something useful.

Pity us poor US DBAs — safely secure using our ancient, many-times-upgraded Oracle 6 databases with their US7ASCII character sets.

We knew that ASCII only covered 0-127, but who could blame us when we started putting WE8MSWIN1252 "international" characters into those fields — the database let us, and it felt kind of sexy putting in cool European characters with umlauts and accents on them.

Besides, all of our business was with other American companies, and if someone had some "funny" characters in their name, then they just had to change them!

Of course, all of this is said with tongue firmly planted in cheek.  Nowadays you’d better be able to handle Unicode in your database if you want to have a prayer of not being labeled as something older than teleprinters and typewriters.

I first encountered this situation when working with a US7ASCII database where we started using XMLTYPE columns — little did I know that XMLTYPE columns actually validated the character set of the XML document coming in — one of our fields was the country name.

Everything was fine until February 13th, 2004 — the day ISO added an entry for the Aland Islands… (which has an A with a diacritical ring above it).

We started seeing errors inserting our XML documents — all due to strict validation of the character set.  Did we change character sets?  No — we stopped using the XMLTYPE columns

Fast forward a few years and now I’m lucky enough to work with proper databases created with the AL32UTF8 character set — so now I can store my friend Mogens Noorgard name correctly (or I would if I could spell it…)

However, little did I realize that I needed to declare my columns differently…

You see, back in the day, VARCHAR2(10) meant that I wanted to store up to 10 characters in the column gosh darn it — I didn’t worry about bytes vs. characters — same thing right?

Er, no.

So in a brand new database with an AL32UTF8 character set, why was I getting column length errors trying to insert the string H,U,”Y with an umlaut” into a VARCHAR2(3) field?

Heck, isn’t “Y with an umlaut” just another character? It’s just WESMSWIN1252 character 255, right?

Don’t tell me it’s a character set issue — I’ve been trying to avoid opening up that NLS manual for years…

Ok, ok — open the manual and start reading about Unicode — specifically UTF-8.  Uh-oh, I read the words "variable-length encoding" and the light starts to dawn…

Turns out that “Y with an umlaut” is 1 byte in WESMSWIN1252 (specifically 0xFF), but it’s 2 bytes in UTF-8 (0xC3BF).

But didn’t I declare the column to be 3 characters in length?  So why does it care about the underlying encoding?

Enter NLSLENGTHSEMANTICS and the fact that the default is set to BYTE.

From the documentation:

http://download.oracle.com/docs/cd/E1188201/server.112/e10729/ch3globenv.htm#NLSPG235

NLSLENGTHSEMANTICS

Property
Description

Parameter type
String

Parameter scope
Environment variable, initialization parameter, and ALTER SESSION

Default value
BYTE

Range of values
BYTE or CHAR

By default, the character data types CHAR and VARCHAR2 are specified in bytes, not characters. Hence, the specification CHAR(20) in a table definition allows 20 bytes for storing character data.

This works well if the database character set uses a single-byte character encoding scheme because the number of characters is the same as the number of bytes. If the database character set uses a multibyte character encoding scheme, then the number of bytes no longer equals the number of characters because a character can consist of one or more bytes. Thus, column widths must be chosen with care to allow for the maximum possible number of bytes for a given number of characters. You can overcome this problem by switching to character semantics when defining the column size.

NLSLENGTHSEMANTICS enables you to create CHAR, VARCHAR2, and LONG columns using either byte or character length semantics. NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based. Existing columns are not affected.

You may be required to use byte semantics in order to maintain compatibility with existing applications.

NLSLENGTHSEMANTICS does not apply to tables in SYS and SYSTEM. The data dictionary always uses byte semantics.

Note that if the NLSLENGTHSEMANTICS environment variable is not set on the client, then the client session defaults to the value for NLSLENGTHSEMANTICS on the database server. This enables all client sessions on the network to have the same NLSLENGTH_SEMANTICS behavior. Setting the environment variable on an individual client enables the server initialization parameter to be overridden for that client.

Sigh…

Can anyone tell me why the default would be BYTE?  Why would I want to declare character fields with BYTE lengths?  Thank goodness it’s not in bits…

Anyway, we adjusted our standard to make sure that DDL always specifies BYTE or CHAR in the declaration now:

VARCHAR2(10 CHAR) instead of VARCHAR2(10), so now we can be sure…

The title pretty much says it. In Oracle 11.2, if you have the Diag+Tuning Pack licenses and the SQL monitoring kicks in for your SQL statement, then instead of the old fashioned ERRORSTACK dump reading you can just query the V$SQL_MONITOR.BIND_XML to find the values and metadata of your SQL statement’s bind variables.

I’ve written an example here:

• http://tech.e2sn.com/oracle/troubleshooting/oracle-s-real-time-sql-monitoring-feature-v-sql_monitor

And a related comment – V$SQL_BIND_CAPTURE is not a reliable way for identifying the current bind variable values in use. Oracle’s bind capture mechanism does not capture every single bind variable into SGA (it would slow down apps which run lots of short statements with bind variables). The bind capture only selectively samples bind values, during the first execution of a new cursor and then every 15 minutes from there (controlled by _cursor_bind_capture_interval parameter), assuming that new executions of that same cursor are still being started (the capture happens only when execution starts, not later during the execution).

If you are flying cattle-class it’s pretty easy to get annoyed, but here are a few things that really get on my nerves…

Hand Luggage

• The correct way to remove your luggage from the overhead compartment does not include bouncing it off my head! The item in question was a solid hand-luggage bag with reinforced corners. The word, “Shit”, came out of my mouth at high volume as I checked my head for bleeding.
• When they tell you one piece per passenger, that is what they mean. Not three pieces of hand luggage and a suit holder. If you are going to bring that much with you (assuming you are let on the flight with it), don’t you dare complain about there being no room in the over head compartment above your seat you selfish git.
• Hand luggage does not mean full size cases, fragile items or wedding cakes. If you do decide to attempt to transport a wedding cake as hand luggage, don’t get all uptight with the flight attendants because there is nowhere safe to put it.

Aisle Etiquette

• When you are walking up the aisle, don’t grab my head rest and pull on it with all your might. It’s really annoying, especially when I have raised my head rest and in grabbing it you push it back down again.
• When you are walking past the exit row seats, don’t steady yourself on my pull-out screen so your fingers cover half the screen and leave what I can only assume is post-toilet visit residue on the screen.
• When you are walking up the aisle, don’t grab my shoulder to steady yourself. It’s even more annoying than the head rest or the screen incidents.
• If you insist on sleeping with your legs and/or head blocking the aisle, don’t give me (or the flight attendants) evils if I/they wake you up as I/they walk past.

Headphones

• When you see the flight attendent coming to serve you food or drinks, remove your headphones! Don’t sit there squinting at him/her saying, “What?”, repeatedly. The reason you can’t hear them is you have the volume set to 11/10 on your earphones you daft old bat.
• Remove your headphones before starting a conversation with your partner. The rest of the cabin are not interested in that “funny” scene in the rom-com you are watching. If they were I’m sure the captain would announce it.

Hygiene

• Use deodorant. Nuff said.
• When you are sitting next to me, don’t constantly huff and puff and generally cover me with your stank breath. I’m not enjoying the flight either, but I’m not getting on your tits.
• Leave the toilets in the state you would expect to find them. I’m sick of having to clean them up for fear of the next person thinking it’s me that made that mess. They do provide bins you know!
• If you are going to cough, sneeze or hock a loogie please use a tissue. I am constantly ill these days and I’m guessing most of it comes from the dirty air in planes.

Flight Attendants

• You may not know this, but the flight attendants are not your personal slaves. If you get off your lazy fat ass once in while you might manage to avoid DVT.
• When the flight attendant asks you to do something, you should do it. That includes not getting out of your seat to retrieve your hand luggage while the plane is still moving.

Seats

• When you see the flight attendants serving food, it’s a nice gesture to put your seat upright. It makes life a lot easier for the person behind you.
• Just because you’ve finished eating it doesn’t mean the person behind you has, so you might want to think twice before attempting a new world record for speed seat reclining.
• I’ve paid for my seat so don’t steal my space by leaning on me or sticking your elbows into my ribs. I’m fat and if I can manage to stay within my allotted space you sure as hell can.

Volume

• You don’t need to shout to talk to the person sitting next to you (see headphones).
• Business people. I’m sure your companies and your clients would not be pleased to know the amount of information you are passing out to strangers by speaking loudly about your latest deal/budget. I sat behind two guys recently who were obviously playing, “my job is more important that yours”, with each other. During several minutes I was clearly able to hear the companies they worked for, the clients their companies were working with and some of the deals they had struck, including the profit margins involved. I was severely tempted to write it all down and phone the companies in question and tell them what I had heard. In summary, it’s not big, it’s not clever and it makes you look like an insecure prick!

Exiting the Plane

• Removing your hand luggage while the plane is moving is a bad idea. I’m sure if you injure someone doing it you could find yourself with a hefty legal bill.
• When the time comes to leave the plane, pushing and shoving to try and move one space forward in the queue is really stupid. If you are lucky you will get out 3 seconds earlier, where you will find yourself in a queue for customs and baggage claim. It’s really not worth the effort of trampling me.

These and many more annoying habits can be found in the economy class seating of your favorite airline.

Cheers

Tim…