Why does Oracle 9i treat an empty string as NULL?

by

I believe the answer is that Oracle is very, very old.

Back in the olden days before there was a SQL standard, Oracle made the design decision that empty strings in VARCHAR/VARCHAR2 columns were NULL and that there was only one sense of NULL (there are relational theorists that would differentiate between data that has never been prompted for, data where the answer exists but is not known by the user, data where there is no answer, etc. all of which constitute some sense of NULL).

By the time that the SQL standard came around and agreed that NULL and the empty string were distinct entities, there were already Oracle users that had code that assumed the two were equivalent. So Oracle was basically left with the options of breaking existing code, violating the SQL standard, or introducing some sort of initialization parameter that would change the functionality of potentially large number of queries. Violating the SQL standard (IMHO) was the least disruptive of these three options.

Oracle has left open the possibility that the VARCHAR data type would change in a future release to adhere to the SQL standard (which is why everyone uses VARCHAR2 in Oracle since that data type’s behavior is guaranteed to remain the same going forward).

More Related Contents:

  1. Splitting string into multiple rows in Oracle
  2. Determine Oracle null == null
  3. SQL not displaying null values on a not equals query?
  4. The most efficient way to UPPER CASE two columns in a single query [closed]
  5. Current date in Oracle Db
  6. Merging two tables in SQL [closed]
  7. I want to search job and salary from employee table but I am getting error like invalid relational operator [closed]
  8. Select rows which are not present in other table
  9. Split comma separated values to columns in Oracle
  10. How to load a large number of strings to match with oracle database?
  11. Oracle SELECT TOP 10 records [duplicate]
  12. MySQL equivalent of DECODE function in Oracle
  13. Select a random sample of results from a query result
  14. SQL error “ORA-01722: invalid number”
  15. Disable all table constraints in Oracle
  16. Get month name from date in Oracle
  17. ORA – 00933 confusion with inner join and “as”
  18. SQL Error: ORA-00942 table or view does not exist
  19. How to count the number of occurrences of a character in an Oracle varchar value?
  20. Comparing with date in Oracle sql
  21. IN Clause with NULL or IS NULL
  22. SQL selecting rows by most recent date with two unique columns
  23. How to extract group from regular expression in Oracle?
  24. How to return multiple rows from the stored procedure? (Oracle PL/SQL)
  25. How to extract week number in sql
  26. In Oracle, is it possible to INSERT or UPDATE a record through a view?
  27. How to handle optional parameters in SQL query?
  28. Oracle DB: How can I write query ignoring case?
  29. ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)
  30. Oracle Convert TIMESTAMP with Timezone to DATE

Leave a Comment