Re: Query Clarification.

Hehe, he is hitting kind of bug in Oracle, having a little rant now ... . Empty and null are treated as same kind of coffee in SQL statements but not in PL/SQL.

An empty glass of water is still a glass. Try that in SQL and will tell you that:

INSERT '' INTO .... will never retrieve SELECT ..... FROM ... WHERE ... = ''

In my eyes that is a bug. OK, try WHERE NVL(....,'') = '' or WHERE NVL(.....'x') =' x'. Now this little bugger will force a full table scan on say an highly selective value of a column. NULL is not EMPTY, but treated the as same one time and not the other time.

Try this in PL/SQL (sorry to post some beginner examples)
.....

v_var VARCHAR2(200);
BEGIN
....

v_var := v_var || some_text;
....

END;  but

.....

v_var VARCHAR2(200);
BEGIN
....

v_var := '';
v_var := v_var || some_text;
....

END; OK go back to SQL

SELECT a.something, 'Hello'|| b.something ||' world' FROM a, b
WHERE a = b(+)
AND .... or

SELECT a.something, 'Hello'|| NVL(b.something,'') ||' world' FROM a, b
WHERE a = b(+)
AND .... Sometimes that can be very tricky, especially when there are non printable characters involved. The whole design comparision between empty and null is flawed and inconsinstant.

Take a web page for example

www.mysite.com/pls/webesite?var_in=

In PLS/SQL:
PROCEDURE webesite(var_in VARCHAR2 DEFAULT NULL) ,,,,
IF var_in IS NULL THEN

        HTP.P('HELLO WORLD');
END IF; No joy, it will not do it.

Now:
IF NVL(LENGTH(var_in),0) = 0 THEN

        HTP.P('HELLO WORLD');
END IF; will always work, no matter if that variable is passed empty or not at all.

Back to SQL
SELECT ...
FROM ...
WHERE NVL(col,'XXXXXXXXX') = 'XXXXXXXXX'

Now create a function based index
CREATE INDEX tab_fb_col_idx ON tab
( NVL(col,'XXXXXXXXX'))

Viola, instead of having a full table scan on tab it will pick up that index straight away, if desired is another question.

... WHERE col IS NULL => WHERE NVL(col,'XXXXXXXXX') = 'XXXXXXXXX'

... WHERE col IS NOT NULL =>WHERE NVL(col,'XXXXXXXXX') != 'XXXXXXXXX' In applications there are a some cases (not many) where an empty string is passed as an additional parameter, so it would be nice that Oracle would be clever enough to see an EMPTY string as NULL and treat them like this. It's a hassle to test that a parameter is empty and than to use IS NULL, IS NOT NULL instead of a simple "a = :b", "a != :b" for all cases.

Comparing NULL = NULL, NULL <> NULL, NULL > NULL, etc. will always result in false (another paradox for the first case), however all empty columns are NULL even when using UPDATE tab SET col = '', but on the other hand it makes a big differene in PL/SQL. It's just very confusing and easily to fall for it, even having +++ years experience, still sneaking in or forgetting about, bugger.

I hope above samples clear it up a little bit. Received on Sun Oct 30 2005 - 01:20:57 CDT