Oracle SQL - NULL Values and Zero Strings

Following ANSI SQL 1992 standard empty or zero lenght strings and NULL are different. So I was a little bit surprised after executing the following queries on an Oracle 11R2 database last days:

a) As a Result:
select length(null) from dual; -- NULL as expected
select length('') from dual;     -- NULL too, what was a bit unexpected
select length(' ') from dual;    -- 1 as expected

 b) In a Comparison:
select dummy from dual where '' = '';         -- Instead of 'X' no result, comparison failed
select dummy from dual where '' = NULL;  -- Same as above, not a real surprise
select dummy from dual where '' IS NULL; -- The expected result 'X'


Searching at "Ask Tom" I found the following statement:

and we said...
A zero length varchar is treated as NULL.
'' is not treated as NULL.
'' when assigned to a char(1) becomes ' ' (char types are blank padded strings).
 '' when assigned to a varchar2(1) becomes '' which is a zero length string and a zero length string is NULL in Oracle (it is no long '')


In the Oracle SQL Language Reference I found in addition the following comment regarding the difference of VARCHAR and VARCHAR2:

VARCHAR Datatype
Do not use the VARCHAR datatype. Use the VARCHAR2 datatype instead. Although the VARCHAR datatype is currently synonymous with VARCHAR2, the VARCHAR datatype is scheduled to be redefined as a separate datatype used for variable-length character strings compared with different comparison semantics.


So what does that all mean from a practical point of view:

1. Oracle does not handle null values for VARCHAR ANSI SQL 92 conform as the empty string is converted to a null value.
2. Always use VARCHAR2 out of compatibility reasons as for VARCHAR Oracle has reserved the right to change the comparison semantics at a later stage to become ANSI SQL 92 compatible.
3. For all CHAR types due to padding for '' everything is padded with spaces.
4. The behaviour of empty or zerlo length Strings described is the same in all Oracle releases.

This behaviour could become important when we think about query design for Database Optimization and Tuning. Enjoy & experiment on your own!

Oracle Wait Event Forcasting now implemented!

The forcast functionality has been released for QueryAdvisor. It is now possible to collect and analyze Oracle Wait Events on Oracle instance and session level over long periods of time.

The results of the already existing funtionality for advanced Oracle instance and session analysis are now collected and automatical sorted by database instance in the background. This offers the possiblity to dedect changes in the type, amount and execution-time of Oracle Wait Events on instance and session level.

As possible restarts of the database instance are automatical dedected, the quality of the calculation is exceptional as ambiguous data could be filtered out upfront. The results are graphical presented and could be exported in addition.

A trial copy of our release of QueryAdvisor is waiting for You!

SQL*Net and Transparent Network Substrate

SQL*Net is the software provided by Oracle to establish connections between local Oracle database clients and the related Oracle database instance or between two different Oracle database instances via database link. It is based on the Transparent Network Substrate (TNS) protocol.

TNS is a generic logical protocol which could be executed on top of all common physical network protocols such as TCP/IP. It is based on individual logic packets which are mapped transparently to physical packets.

TNS must be able to handle the different Oracle database server connectivity scenarios:
- shared server database instance
- dedicated server database instance
- dedicated server bequeath connection
  (database client and database instance must be on the same hardware)

Visit the QueryAdvisor Knowledge Center to read more about the Transparent Network Substrate.