Regarding "Why does Oracle recommend using VARCHAR2 instead of VARCHAR datatypes?", I challenge the answer given. I have worked with Oracle 5, 6, 7, 7.2, 8.0, and 8.1, and although the early versions of Oracle are hard to remember, it has always been true that a zero-length string and NULL are treated the same for the varying-length character data type (VARCHAR2). Perhaps you had the "CHAR" type in mind, which is a fixed-length character data type; here an empty string would be padded with blanks to the declared length, and was definitely not the same as NULL.
You are correct, to a point. I had CHAR in mind, instead of VARCHAR2. Thanks for pointing that out.
But Note:1011340.6 on Metalink does state that the the behavior of zero-length strings and NULLS changed by Oracle 7.1.3 all the way through Oracle 8.1.7. And if you do some further digging, how Oracle handles zero-length strings really only applies to inserting the data. Try issuing the following queries (I tested this in Oracle 8, 8i, and 9i):
SELECT * FROM my_table WHERE column_name IS NULL; SELECT * FROM my_table WHERE column_name='';They will return two different results! So not all handling of zero length strings is the same. I recall reading somewhere along the line that Oracle Corp. was going to change this behavior in 9i. Apparently, my information was in error. Thanks for pointing it out!
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.