Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle considers empty strings to be NULL while SQL Server does not - how is this best handled?

Tags:

I have to write a component that re-creates SQL Server tables (structure and data) in an Oracle database. This component also has to take new data entered into the Oracle database and copy it back into SQL Server.

Translating the data types from SQL Server to Oracle is not a problem. However, a critical difference between Oracle and SQL Server is causing a major headache. SQL Server considers a blank string ("") to be different from a NULL value, so a char column can be defined as NOT NULL and yet still include blank strings in the data.

Oracle considers a blank string to be the same as a NULL value, so if a char column is defined as NOT NULL, you cannot insert a blank string. This is causing my component to break whenever a NOT NULL char column contains a blank string in the original SQL Server data.

So far my solution has been to not use NOT NULL in any of my mirror Oracle table definitions, but I need a more robust solution. This has to be a code solution, so the answer can't be "use so-and-so's SQL2Oracle product".

How would you solve this problem?

Edit: here is the only solution I've come up with so far, and it may help to illustrate the problem. Because Oracle doesn't allow "" in a NOT NULL column, my component could intercept any such value coming from SQL Server and replace it with "@" (just for example).

When I add a new record to my Oracle table, my code has to write "@" if I really want to insert a "", and when my code copies the new row back to SQL Server, it has to intercept the "@" and instead write "".

I'm hoping there's a more elegant way.

Edit 2: Is it possible that there's a simpler solution, like some setting in Oracle that gets it to treat blank strings the same as all the other major database? And would this setting also be available in Oracle Lite?

like image 811
MusiGenesis Avatar asked Oct 02 '08 04:10

MusiGenesis


People also ask

How do you handle an empty string in Oracle?

Handling empty strings in PostgreSQL In Oracle, because empty strings are treated as NULL, the preceding insert statements #2 and #3 will store NULL for column tname in the table. However, in PostgreSQL, the table will store NULL for the #2 and an empty string for the #3 insert statements.

Is empty string considered NULL in Oracle?

Oracle reads empty strings as NULLs, while PostgreSQL treats them as empty. Concatenating NULL values with non-NULL characters results in that character in Oracle, but NULL in PostgreSQL. Oracle and PostgreSQL behave similarly in many cases, but one way they differ is in their treatment of NULLs and empty strings.

Does SQL Server treat empty string as NULL?

What is NULL? NULL is used in SQL to indicate that a value doesn't exist in the database. It's not to be confused with an empty string or a zero value. While NULL indicates the absence of a value, the empty string and zero both represent actual values.

How does Oracle treat NULL?

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls. Any arithmetic expression containing a null always evaluates to null.


3 Answers

I don't see an easy solution for this.

Maybe you can store your values as one or more blanks -> ' ', which aren't NULLS in Oracle, or keep track of this special case through extra fields/tables, and an adapter layer.

like image 75
Camilo Díaz Repka Avatar answered Oct 14 '22 11:10

Camilo Díaz Repka


My typical solution would be to add a constraint in SQL Server forcing all string values in the affected columns to have a length greater than 0:

CREATE TABLE Example (StringColumn VARCHAR(10) NOT NULL)

ALTER TABLE Example
ADD CONSTRAINT CK_Example_StringColumn CHECK (LEN(StringColumn) > 0)

However, as you have stated, you have no control over the SQL Database. As such you really have four choices (as I see it):

  1. Treat empty string values as invalid, skip those records, alert an operator and log the records in some manner that makes it easy to manually correct / re-enter.
  2. Convert empty string values to spaces.
  3. Convert empty string values to a code (i.e. "LEGACY" or "EMPTY").
  4. Rollback transfers that encounter empty string values in these columns, then put pressure on the SQL Server database owner to correct their data.

Number four would be my preference, but isn't always possible. The action you take will really depend on what the oracle users need. Ultimately, if nothing can be done about the SQL database, I would explain the issue to the oracle business system owners, explain the options and consequences and make them make the decision :)

NOTE: I believe in this case SQL Server actually exhibits the "correct" behaviour.

like image 44
Dr8k Avatar answered Oct 14 '22 10:10

Dr8k


Do you have to permit empty strings in the SQL Server system? If you can add a constraint to the SQL Server system that disallows empty strings, that is probably the easiest solution.

like image 42
Justin Cave Avatar answered Oct 14 '22 10:10

Justin Cave