null string problem 2

[princecharm]

in my application which is written in vb i get null valu es from oracle and it causes to runtime error. i dont want to check isnull function to check values of variables.

Is there any configuraion parameter in Oracle server to empty string instead of null values.

Regards

 

[ragolia]

Ok, this is it before I walk away from here...

What I was pointing out with my last is this...
The row inserted by this insert
INSERT INTO DEPT VALUES(13,'TEST5','');

is retrieved by this query
SELECT * FROM DEPT WHERE LOC IS NULL;

AND not retrieved by the query
SELECT * FROM DEPT WHERE LOC = '';

The implications here are two things...
1. An empty string is the same as a NULL on insert.
2. An empty string is not the same as a NULL in retrieval.

There are, in my view, conflicting rules on data interpretation here.

 

[SantaMufasa]

Rick,

Sorry, I wouldn't be able to sleep tonight if I let you off without refuting your statement, "...There are, in my view, conflicting rules on data interpretation here." There is no difference in DATA (DATA, DATA, DATA, DATA) between "Null" and [ '' ] since both represent a zero-length, lack of bits. The difference you see is in SYNTAX, (SYNTAX, SYNTAX). Your example, "SELECT * FROM DEPT WHERE LOC = '';" produces identical results to "SELECT * FROM DEPT WHERE LOC = NULL;" (No rows selected). "SELECT * FROM DEPT WHERE LOC IS NULL;" would produce the same results as "SELECT * FROM DEPT WHERE LOC IS '';" if Oracle allowed that syntax, but the only word that can follow "IS" in a WHERE clause is "NULL".

Now I can go to sleep and hopefully you can rest easier that NULL and '' represent the same lack of data.

Cheers,

Dave
Sandy, Utah, USA @ 06:47 GMT, 23:47 Mountain Time

 

[SantaMufasa]

Pardon: I should have said, "the only EXPRESSION that can follow "IS" in a WHERE clause is "NULL".

 

[andyclap]

Aha, but surely that's the whole point of this little discussion...

I like the concept of NULL representing 0 bits of information; however '' does not represent 0 bits of information. While '' may be a zero length string, it is a string constant, and therefore there is some information here - its type (string) and length (0). You wouldn't argue that 0 is NULL, yet '' is no more representable by 0 bits of information than 0. Thus there is some inherent information in any typed value, and therefore any typed value cannot be 0 bits of information. '' is not null.

Oh well, we can debate this until we're blue in the face, the fact of the matter is Oracle considers '' to be NULL, and we programmers have to work around it until they eventually put a flag on varchar columns to indicate 'proper' null behaviour. Or just allow NOT NULL varchar columns to take '' and behave accordingly in comparisons.

 

[SantaMufasa]

Andy,

If Oracle (the engine) really believed (as you do) that '' is "...type (string) and length(0)...", then a
"SELECT DUMP('') from dual;" would return
"Typ=96 Len=0", but it doesn't; it returns:
"NULL".

By definition and convention, Oracle made '' behave the same as NULL, except for the comparison operations "IS NULL" and "IS NOT NULL".

Amen.

Dave
Sandy, Utah, USA @ 07:10 GMT, 00:10 Mountain Time

 

[andyclap]

Yes, absolutely agreed - I'm not debating this at all.
I'm pointing out that from a programmer's perspective Oracle's behaviour is incorrect.

 

[SantaMufasa]

Andy,

So we are all clear on this, which "Thou shalt not..." commandment did Oracle disobey from a "programmer's perspective"?

Dave
Sandy, Utah, USA @ 18:31 GMT, 11:31 Mountain Time

 

[carp]

THIS programmer thinks of NULL as data nirvana - a true state of nothingness; Oracle's implementation poses no conflict for me!

 

[ragolia]

All

I have to say this. Between the last few responses, it looks to me a little like this conversation has taken on deep philosophical connotations. All because Oracle syntax is constrained in such a way as to impose multiple interpretations of the same data (or lack thereof).

Wow - that was a mouthful that really didn't say anything...

 

[andyclap]

SantaMufasa - in the words of the Lord Codd almighty ...

Commandment 3: Systematic Treatment of Null Values
A field should be allowed to remain empty. This involves the support of a null value, which is distinct from an empty string or a number with a value of zero.

Interestingly I have actually seen Codd's rules referred to on the internet as Codd's commandments

 

[SantaMufasa]

Andy, touché...You found a "chapter and verse." But unfortunately, the Kingdom of Database is multitheistic, with one of the gods presiding at Oracle [Read, "The Difference Between God and Larry Ellison (God Doesn't Think He's Larry Ellison)" by Mike Wilson, $11.17 from Amazon.com] As you know, Lord Larry has not let Lord Codd damage Lord Larry's own image or product behavior. It's kinda like the Great Schizm between the Roman and Greek Churches in A.D. 1054. But I digress.

In any case, I understand the basis of your "faith" now and why you believe in NULLs the way you do.

Dave
Sandy, Utah, USA @ 02:50 GMT, 19:50 Mountain Time

 

[andyclap]

I'll have to check out that book

Anyway, if we go back to the root question of this topic (working with Oracle strings in VB), the answer really is that: Yes, this is the defined behaviour of Oracle, and you have to work with it at an application level by treating blank string as a special case of NULL. If you bear it in mind when designing your application it's not too difficult to cope with.

Handy hint for the question poster - many people use
sMyString = rsRecordset.fields("fieldname&quot.value & ""
This uses VB's impure null handling to counter Oracle's impure null handling. Such is life.

The system I am currently developing was originally based around SQL Server but has recently been rolled out to two large clients with an underlying Oracle database with only minor changes. Fiddly changes they may have been, but the moral of the story is: you can always find a workaround.

 

[ragolia]

The hint as posted by andyclap is I believe valid for any db when using VB as a front end. I have run into the need to use that same algorithm with VB as a front end to an Access DB (very small # of users) and SQL Server.