Thursday, November 12, 2009

Why Oracle Sux

DECLARE
v_First VARCHAR2(20) := NULL;
v_Second VARCHAR2(20) := '';
v_Third VARCHAR2(20) := 'Something';
v_Blank VARCHAR2(2) := '';
v_Junk VARCHAR2(2) := '&~';
BEGIN
-- you need to run the following once, on it's own to make DBMS_OUTPUT.PUT_LINE work
-- what a shame there is no PRINT statement like a real database
-- SET SERVEROUTPUT ON;

-- proving NVL does not work with '' as the value
IF NVL(v_First,'')  = '' THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = "" Works'); END IF;
IF NVL(v_Second,'') = '' THEN DBMS_OUTPUT.PUT_LINE ( '"" = "" Works'); END IF;
IF NVL(v_Third,'') <> '' THEN DBMS_OUTPUT.PUT_LINE ( 'Something != "" Works'); END IF;
-- proving NVL does not work with a variable containing ''
IF NVL(v_First,v_Blank)  = v_Blank THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = Blank Works'); END IF;
IF NVL(v_Second,v_Blank) = v_Blank THEN DBMS_OUTPUT.PUT_LINE ( '"" = Blank Works'); END IF;
IF NVL(v_Third,v_Blank) <> v_Blank THEN DBMS_OUTPUT.PUT_LINE ( 'Something != Blank Works'); END IF;
-- proving that Oracle is junk, as other <> '' values do the trick
IF NVL(v_First,v_Junk)  = v_Junk THEN DBMS_OUTPUT.PUT_LINE ( 'NULL = Junk Works'); END IF;
IF NVL(v_Second,v_Junk) = v_Junk THEN DBMS_OUTPUT.PUT_LINE ( '"" = Junk Works'); END IF;
IF NVL(v_Third,v_Junk) <> v_Junk THEN DBMS_OUTPUT.PUT_LINE ( 'Something != Junk Works'); END IF;
END;
/
 
RESULT:
NULL = Junk Works
"" = Junk Works
Something != Junk Works

 

5 comments:

Simeon Pilgrim said...

Hi Andrew,

I would first test your tests,

with:

IF '' = '' THEN DBMS_OUTPUT.PUT_LINE ( '"" = "" Works'); END IF;

IF '' <> '' THEN DBMS_OUTPUT.PUT_LINE ( '"" <> "" Works'); END IF;

As empty string is null, you cannot test for equality with null.

To test just NVL do it like:

DBMS_OUTPUT.PUT_LINE ( NVL( v_First, v_Junk) );
DBMS_OUTPUT.PUT_LINE ( NVL( v_Second, v_Junk) );
DBMS_OUTPUT.PUT_LINE ( NVL( v_Third, v_Junk) );

And you should get the results junk, junk, something, showing that NVL is working.

Mr Dee said...

according to

http://www.oracleoverflow.com/questions/5/how-can-i-tell-the-difference-between-null-and-an-empty-string

"In Oracle a zero length varchar gets converted to NULL... it's no longer an empty string."

Is it just me or is this INSANE

Mr Dee said...

OK, database 101. NULL is not the same as empty string.
NULL is unknown. Empty string is 'I know this has no value'

If a database states a name is [John] [NULL] [Doe] then what is the middle name? Answer - we do not know yet.
If a database states a name is [John] [] [Doe] then what is the middle name? Answer - they do not have a middle name.

BLANK <> NULL

Ditto numerics - is Zero the same as NULL? NO WAY.
So why do something this lame to string values?

Simeon Pilgrim said...

Ok, so now I see your issue, where-as before I was just thinking you didn't know how to use NVL or NULL correctly.

I agree that to a OO language like C# turning a empty string to a null is a big issue, but in DB land it doesn't rock my boat so badly.

Unless of course your wanting to know the user entered an empty string.

What would be interesting to know is the behaviour of put an empty sting into a table column of varchar that is also marked NOT NULL.

Mr Dee said...

Easy, a blank value IS NOT NULL. field should hold this no problem.

Post a Comment