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

 

[Turkbear]

Hi, In Oracle an empty string IS a NULL...

A blank string however, is not ( blank means having 1 or more spaces in it)

"" is NULL
" " Is Not

So you can add a where clause to your query to eliminate both ( or just true NULLs )from the data returned by it:

Where (fieldname is not null and fieldname > '0')

will eliminate both.

Use either one alone to pick one or the other to eliminate.

 

[princecharm]

well after a qui,ck search on google i saw that empty string means NULL in oracle. This is a non standard beahaviour of ORACLE. I have to port my application oracle but this null troubles me a lot.

how can i correct it in my application. I cant write a function to eliminate null values as my application is so big.
Is there any possibility to make this with "create trigger" or another way...?

regards

 

[stefanhei]

I guess the easiest way is to create views on the tables you use with a NVL-Funktion on the fields that give you trouble, then query against that views.

Stefan

 

[andyclap]

It's one of the fundamental flaws of Oracle. A blank string is treated as null.

This is just plain wrong: Somebody who designed the DB engine didn't pass their database theory 101.

You have to check for blank strings using is null, this means that your varchar fields no longer have a logical null state.

Even simple things like ''='' return false
according to Oracle ''<>'', but '' is null is true!

I just wish there were a table level flag to switch this stupidity off.

 

[andyclap]

incidentally
> blank means having 1 or more spaces in it
You've been working with oracle too long ;-) 1 or more spaces in a string means it is a string of one or more spaces.

 

[avjoshi]

andyclap,

How do you define logical null? All this time I thought that void or blank string is null. Is that wrong?

 

[SantaMufasa]

Both from Miriam-Webster and Oxford: &quot;NULL, having no value&quot;. One or more Zeroes are VALUES; one or more blank spaces are VALUES; if you don't believe me, then ask the computer:

select dump(0) from dual;
DUMP(0)
----------------
Typ=2 Len=1: 128

select dump(' ') from dual;
DUMP('')
----------------
Typ=96 Len=1: 32

select dump(null) from dual;
DUMP
----
NULL <--- no length, no value.

(I know I'm using Oracle to prove Oracle's validity, but we have a valid ISSUE here.) Both 'zero' and 'space' have ASCII values and length. Anything with an ASCII value or length is a KNOWN value. NULL is the absence of value, an unknown value. The only REAL way to have an unknown value from a computing perspective is to have &quot;no bits&quot;, a zero-length quality. If &quot;database theory 101&quot; calls something else NULL, then what does &quot;database theory 101&quot; call the absence of value?

Dave

 

[Turkbear]

HI,
NULL is NULL ( that is , undeterminable or unknown).
An EMPTY string is NULL ( in Oracle land) but a Blank string ( one with 1 or more blank spaces) is not..

EMPTY string: ""
Blank string: " "

NULL is an odd thing but, once you grab the concept that it means 'indeterminate/unknown', the need for special handling is clearer..NULL is only comparable with IS NOT or IS since the only thing we can determine about it is if it exists or not...

Its almost metaphysical....

 

[andyclap]

>If &quot;database theory 101&quot; calls something else NULL, then what does &quot;database theory 101&quot; call the absence of value?

you're probably misinterpret my message ... &quot;Database theory 101&quot; absolutely calls NULL the absence of value. It has no value therefore nothing equals NULL (not even NULL therefore you have to test for it with is null).

The crux of the matter is that in programming terms, '' is a value. When programming against Oracle, you have to add in extra logic to either treat NULLs as blank strings, or blank strings as NULLs; either making NULL a value, or treating a blank string as the absence of value.

 

[SirCharles]

So, how would you prevent having to use the is null is not null comparisons using Oracle? Or better question, what kind of standard should be used to translate MySQL to Oracle when dealing with the empty string ''? Should all selects, updates, inserts and deletes be modified to add an additional check for nulls?
or just replace all checks for the empty string with is null or is not null?
ex. select * from designer where username is null
would replace
select * from designer where username = ''. Seems easy enough. BTY: what is that between the ticks? Oh yea, nothing.

 

[andyclap]

>or just replace all checks for the empty string with is null or is not null?
>select * from designer where username is null
>would replace
>select * from designer where username = ''. Seems easy enough

Yep, that's about it. But of course, if you're building the SQL in code, using a variable who's value you don't know, you have to use both ways. e.g (Java).

if (mystring=='')
mysql='select * from designer where username is null';
else
mysql='select * from designer where username = ' + quotefunc(mystring);

 

[ragolia]

A little heads up for everyone in this thread. Oracle does make a distinction between NULL and binary zeroes. It is possible to populate a field (particularly CHAR or VARCHAR)with binary zeroes and Oracle will not recognize that as NULL. This is contingent upon the appropriate setting of the NULL indicator byte for that column.

In a given table where col_a contains binary zeroes without the appropriately set indicator byte &quot;select * from tbl_a where col_a is NULL&quot; will not return those rows.

I have been bitten badly by this...

 

[andyclap]

rogolia - interesting... do you mean the actual low-level content of the field here - in which case how would you write transparent access methods for this, and would the db remain performant? Or are you talking about using chr(0) as a string value? Chr(0) is of course, not null.

 

[ragolia]

Ok, I'm about to eat a little crow, I think.
I ran these 3 sql updates as a test...
(The dept table consists of 3 columns
DEPTNO = NUMERIC PRIMARY KEY
DNAME = VARCHAR2
LOC = VARCHAR2)


INSERT INTO DEPT VALUES(14,'TEST6',CHR(0));
INSERT INTO DEPT VALUES(12,'TEST4',NULL);
INSERT INTO DEPT VALUES(13,'TEST5','');

followed by
SELECT COUNT(*) FROM DEPT WHERE LOC = NULL;
This query returned count = 2 (the rows with 'TEST4' & 'TEST5' as the dept names.

but here's an interesting twist
SELECT COUNT(*) FROM DEPT WHERE LOC = '';
returns 0 (zero). So, an empty string is not the same to Oracle as NULL.

I think my screw-up came from my &quot;C&quot; background. NULL to the C language = chr(0). I think I will take my headache and go home.

Rick Agolia
I think I now have a bad taste in my mouth...Crow does not taste very good.

 

[SantaMufasa]

Rick,

Sorry to have to add to your headache and the bad taste in your mouth, but here goes.

First of all, I'd be willing to bet half my paycheck that you did not actually receive the results you said you did with the query:

SELECT COUNT(*) FROM DEPT WHERE LOC = NULL;
(You said, &quot;This query returned count = 2 (the rows with 'TEST4' & 'TEST5' as the dept names.&quot;

Sorry, but there ain't no way in the Oracle world that you saw a count of 2 with that query. It would have to read:

SELECT COUNT(*) FROM DEPT WHERE LOC IS NULL;

...for you to see '2' as the results.

Here's an even more-interesting twist...Try this:

SELECT COUNT(*) FROM DEPT WHERE NULL = NULL;

You will see a result of '0', because NULL does NOT equal NULL; however, NULL IS NULL (and '' IS NULL) !

In Oracle, the operator '=' means (when comparing strings) &quot;do the bits of the left operand match the bits of the right operand?&quot; If either operand has no bits (NULL), then the result of the comparison is immediately (and by definition) &quot;NOT TRUE&quot;

This should make it easier to understand:
NULL is an UNKNOWN VALUE. 1) Does an UNKNOWN VALUE equal an UNKNOW VALUE? The answer is &quot;UNKNOWN&quot; and certainly not &quot;TRUE&quot;. But, &quot;IS an UNKNOWN VALUE an UNKNOWN VALUE?&quot; Now that, unarguably is &quot;TRUE&quot;. That is why, when checking comparing to anything that could possibly be NULL, we must ask &quot;Is the comparand unknown?&quot;. The syntax for asking that question is &quot;...<comparand> IS NULL...&quot;; if you ask &quot;...<comparand> = NULL...&quot;, then the results are not TRUE...the results are UNKNOWN (not FALSE). So in Oracle, there are three possible results to a True/False question: TRUE/FALSE/and MAYBE (read, &quot;NULL&quot; or &quot;UNKNOWN&quot.

So, here are results you'll see against your DEPT table, above:

SELECT COUNT(*) FROM DEPT WHERE LOC = NULL; (answer 0)
SELECT COUNT(*) FROM DEPT WHERE LOC IS NULL; (answer 2)
SELECT COUNT(*) FROM DEPT WHERE LOC = LOC; (answer 1)
SELECT COUNT(*) FROM DEPT WHERE LOC = ''; (answer 0)
SELECT COUNT(*) FROM DEPT WHERE NULL = NULL; (answer 0)
SELECT COUNT(*) FROM DEPT WHERE NULL IS NULL; (answer 3)
SELECT COUNT(*) FROM DEPT WHERE '' IS NULL; (answer 3)

Fun with logic, huh? (Let me know how you feel about all this.)

Dave
Sandy, Utah 8 Oct 03 @ 04:16 GMT (21:16 Mountain Time)

 

[ChrisHunt]

> If either operand has no bits (NULL), then the result of the comparison is immediately (and by definition) &quot;NOT TRUE&quot;

Actually the result of the comparison is not &quot;NOT TRUE&quot;, it's not &quot;TRUE&quot; and it's not &quot;FALSE&quot; - it's &quot;NULL&quot;. Any operator, including boolean operators, that acts upon one or more NULL values (apart from the specialised NVL and IS {NOT} NULL operators) will return a NULL as its answer. Try this PL/SQL...
[tt]
declare
a number := 1;
b number := NULL;

ans boolean;
begin
ans := (a = b);

if ans then
dbms_output.put_line('TRUE');
elsif NOT ans then
dbms_output.put_line('FALSE');
elsif ans IS NULL THEN
dbms_output.put_line('NULL');
end if;
end;
[/tt]


-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk

 

[ragolia]

Dave

Actually Dave, I appreciate when people are willing to add to my headaches. Don't protect me from my screw-ups - I won't learn anything that way.

You are correct in pointing out the error in my query - although I will plead some innocense or lack of typing ability. The query I actually ran checked for &quot; IS NULL&quot; not &quot; = NULL&quot;. This is a case where cntl-c/cntl-p (cut/paste) would have done me a world of good - except I was on 2 seperate machines while I was doing this.

So now I'll take my crow, my headache and go find a deep hole to crawl into - oops, can't do that Missouri is solid rock below the top soil

Heading back toward the point that I was trying to make in the beginning. Empty strings and NULL are not equal to Oracle.

Notice the queries that I did run...

INSERT INTO DEPT VALUES(13,'TEST5','');

The results of this insert are a row with NULL in the LOC column, which would be the same as if I had typed.
INSERT INTO DEPT VALUES(13,'TEST5',NULL);

However,
SELECT COUNT(*) FROM DEPT WHERE LOC = '';
does not return any of the rows inserted.

So in this case an empty string, as identified by '' is not the same as a NULL - although it was treated that way at the point of insert.

 

[SantaMufasa]

Rick,

For clarity's sake, the only difference between an empty string ('') and NULL is syntactical: Oracle's SQL syntax does not allow one to say, &quot;...WHERE <comparand> IS ''...&quot;; it only allows &quot;...WHERE <comparand> IS NULL...&quot;. Otherwise, NULL and '' are interchangeable. For example, on an UPDATE statemtent, you may sat &quot;...SET <column> = ''&quot; or &quot;...SET <column> = NULL&quot; with identical results.

And, yes, Chris, for clarity, my reply should have read '...not &quot;TRUE&quot;...' instead of '...&quot;NOT TRUE&quot;...'. But I was hoping my observation of Oracle's conditional-results options, '...TRUE/FALSE/and MAYBE (read, &quot;NULL&quot; or &quot;UNKNOWN&quot...' redeemed me and match the meaning of your example. (BTW, I wonder if we are relatives...&quot;Chris Hunt&quot;, &quot;Dave Hunt&quot;...we'll need to check on Ancestry.com )

Cheers,

Dave Hunt
Sandy, Utah 09 Oct 03 @ 17:40 GMT, 10:40 Mountain Time

 

[ChrisHunt]

Ah, my long-lost cousin Dave . Yeah, I should have pointed out that your TRUE/FALSE/MAYBE paragraph was the true position (wonder if we could persuade Oracle to introduce &quot;MAYBE&quot; as another synonym for &quot;NULL&quot;, could make for some entertaining code!)

Ragolia: If you still think '' is different to NULL, try my PL/SQL code with
[tt]
a number := '';
b number := '';
[/tt]
If Oracle worked properly (or how you think it works) a=b would be TRUE, in fact a, b and a=b are all NULL.


-- Chris Hunt

http://www.extracon.com

http://www.mcgonagall-online.org.uk

http://www.napitalia.org.uk

http://www.leicesteryha.org.uk