NULLS are sort of tricky. There is some value in the column. Every byte must contain something; something between x’00’ and x’FF’. What makes it NULL to the RDBMS is the NULL Indicator that is appeneded to the data column.
When working with NULLs, you must always keep several things in mind.
> in the WHERE clause always use proper syntax to check the NULL condition – e.g. WHERE col_a IS NULL. Do not make the mistake of saying WHERE col_a = NULL.
> after the row is in your application, always test the NULL Indicator before accessing the column itself.
> PL/SQL syntax for checking for NULL is different than in other code such as COBOL.
> the NVL function can help relieve some of the problems when dealing with NULLs
Hope this helps.
Have you checked the database to determine that there are no corrupt blocks?
While that does not happen often, it is possible for the database to have corrupt blocks – either in the data table or in the index tables. If the index is corrupt, it is possible that a query using an index would falsely return or fail to return records.
You didn’t specify an Oracle version, and there are some different tools and techniques for detecting and repairing problems based on the version. If you Google the following, you will get numerous useful hits:
Oracle repair corrupt block <version>
Oracle repair corrupt block 10g