This discussion is archived
1 2 3 4 26 Previous Next 385 Replies Latest reply: Aug 20, 2007 5:26 AM by Alessandro Rossi Go to original post RSS
  • 15. Re: Treatment of zero-length strings as NULLs?
    511303 Newbie
    Currently Being Moderated
    Yes, these two examples illustrate an inadmissible contradiction from inside Oracle itself. An inconsistency par excellence.

    Niksa
  • 16. Re: Treatment of zero-length strings as NULLs?
    511303 Newbie
    Currently Being Moderated
    Hi Albert

    well, this really begs the question: if my application has to test a value against a null content (is null?), what to use for testing - PL/SQL block or a conventional SQL block, since they return different, opposite results? I'm fairly confused.

    Niksa
  • 17. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    > ... what to use for testing - PL/SQL block or a conventional SQL block, since they return
    different, opposite results?

    Frankly speaking, I really don't know. I am an Oracle's newbie with less than three months of experience with Oracle 10g R2. I am pretty familiar with Sybase-ASE 12.5/15, MS SQL Server 2005 and MySQL 4/5, but not with Oracle. Anyway, seems that a wise and smart "workaround" would be the avoidance of insertions of empty strings as NULLs. If a CHAR/VARCHAR2 column is nullable and you want a field to be NULL, then insert NULL explicitly into the column (not as ''). The same is true with PL/SQL variable assignments. If you do so, there will be no difference between PL/SQL and server SQL results.

    Regards

    Albert
  • 18. Re: Treatment of zero-length strings as NULLs?
    245482 Newbie
    Currently Being Moderated
    I've never liked Oracle's decision to make '' correspond to NULL. It causes edge conditions in text functions that I run into every two or three years. I don't like it. None the less I've grown used to it and consider it one of the few (only?) real warts the Oracle hasn't/can't outgrow.

    That said, I'll take it over the warts I've dealt with in other db vendors' products. Right now I'm dealing with MS SQL Server.
  • 19. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    > I've never liked Oracle's decision to make '' correspond to NULL. It
    causes edge conditions in text functions that I run into every two or
    three years. I don't like it. None the less I've grown used to it and
    consider it one of the few (only?) real warts the Oracle hasn't/can't
    outgrow.

    In any case it's more than warts. Seems that Oracle has totally screwed up definition of the null set from the rest of the world. Unbelievable! Even the result of concatenation operation between null and non-null alphanumeric operands is NOT NULL in Oracle. It would be nice if this forum could put a collective "feature/bug fixing request" to force Oracle to bring its exotic definition of NULL into line with the rest of the world. Otherwise, we will be constantly facing the perls and beauties like this:
    -------------------------------------------------------------------------
    O R A C L E M y S Q L
    -------------------------------------------------------------------------

    CREATE TABLE t1 (                     CREATE TABLE t1 (
    first_name VARCHAR2(10) NULL,         first_name VARCHAR(10) NULL,
    last_name VARCHAR2(12) NULL);         last_name VARCHAR(12) NULL);

    INSERT INTO t1                        INSERT INTO t1
    VALUES ('John', '');                  VALUES ('John', '');
    INSERT INTO t1                        INSERT INTO t1
    VALUES ('Scott, NULL);                VALUES ('Scott, NULL);

    ------------------------------------------------------------------------- 

    SELECT * FROM t1                      SELECT * FROM t1
    WHERE last_name IS NULL;              WHERE last_name IS NULL;               

    FIRST_NAME LAST_NAME                  first_name last_name
    ---------- ------------               ---------- ------------
    John       NULL                       John       NULL
    Scott NULL

    -------------------------------------------------------------------------

    SELECT * FROM t1                      SELECT * FROM t1 
    WHERE last_name = '';                 WHERE last_name = '';

    no rows selected first_name last_name
    ---------- ------------
    John


    -------------------------------------------------------------------------

    SELECT LENGTH(last_name)              SELECT LENGTH(last_name)
    FROM t1;                              FROM t1;

    LENGTH(LAST_NAME)                     length(last_name)
    -----------------                     -----------------
    NULL 0
    NULL                                               NULL

    -------------------------------------------------------------------------

    SELECT CONCAT(first_name,last_name)   SELECT CONCAT(first_name,last_name)
    FROM t1;                              FROM t1;

    CONCAT(FIRST_NAME,LAST_NAME)          CONCAT(first_name, last_name)
    ----------------------------          -----------------------------
    John                                  John
    Scott NULL

    -------------------------------------------------------------------------
    Regards

    Albert
  • 20. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Just Errata:
    -------------------------------------------------------------------------  

    SELECT * FROM t1                      SELECT * FROM t1
    WHERE last_name IS NULL;              WHERE last_name IS NULL;               

    FIRST_NAME LAST_NAME                  first_name last_name
    ---------- ------------               ---------- ------------
    John NULL                       Scott       NULL
    Scott      NULL

    -------------------------------------------------------------------------
    Albert
  • 21. Re: Treatment of zero-length strings as NULLs?
    John Spencer Oracle ACE
    Currently Being Moderated
    Doug has it absolutely correct "Which is right? The first database you work with.
    Which are wrong? The other databases you need to support to make your application "database independent".", although for wrong, I would just say the other databases.

    I'm sure that if I looked at a forum equivalent to this one for other databases, I would find many posts by Oracle people transitioning to that other database whining about "screwed up" implementations of various features. Oracle is what it is and SQL Server is what it is. Each has merits and demerits, you just need to learn to live with them.

    In Oracle, doing:
    SELECT col INTO variable
    FROM table
    WHERE other_col = value
    will throw TOO_MANY_ROWS if there is more than one record matching the criteria. The SQL Server equivalent:
    @variable = SELECT col FROM table
                WHERE other_col = value
    will return the last value returned from the result set. Is this wrong? Coming from an Oracle perspective I would say absolutely yes. However, I have seen posts on this forum from SQL Server people saying why does this throw an error, it works on SQL Server.

    The concept that concatenating NULL to a string returns NULL strikes me as inconvenient at best. You don't know whether I have a middle name or not, so by your definitions, it should be NULL. So, given:
    SQL> CREATE TABLE t1 (first_name  VARCHAR2(10) NULL,
      2                   middle_name VARCHAR2(10) NULL,
      3                   last_name   VARCHAR2(12) NULL);

    Table created.

    SQL> INSERT INTO t1 VALUES ('John', NULL, 'Spencer');

    1 row created.

    SQL> SELECT last_name||', '||first_name||' '||middle_name full_name
      2  FROM t1;

    FULL_NAME
    -----------------------------------
    Spencer, John
    That, at least to me, doesn't mean that my full name should be NULL without jumping through some hoops to deal with NULL.

    I have recently been working on some ETL procedures to pull data out of a SQL Server database into our (Oracle) data warehouse. The fact that SQL Server differentiates between NULL and an empty string caused me no end of grief until I got that fact properly into my head. I didn't whine that SQL Server was wrong, badly designed or inherently flawed, I just recognized the difference and dealt with it.

    "It would be nice if this forum could put a collective "feature/bug fixing request" to force Oracle to bring its exotic definition of NULL into line with the rest of the world" Even if this forum could, or would put in such a request, it will never be changed. There are millions of lines of code out there that would break big time.

    John
  • 22. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    Totally agree with John - Oracle works the way it does, and complaining that other products which AFAIK arrived later have a different behaviour that you happen to prefer won't do any good. However,

    > Even if this forum could, or would put in such a request, it will never be changed. There are millions of lines of code out there that would break big time.

    Surely the approach Oracle would take would be to implement a VARCHAR datatype, and not to alter VARCHAR2 or (if anyone cares) CHAR.
  • 23. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    > if my application has to test a value against a null content (is null?), what to use for testing - PL/SQL block or a conventional SQL block, since they return different, opposite results? I'm fairly confused.

    It is only the INSERT that is different since SQL appears to convert the '' to NULL. This only makes a difference if you are using a CHAR column, and even then it doesn't happen when you use bind variables.
  • 24. Re: Treatment of zero-length strings as NULLs?
    John Spencer Oracle ACE
    Currently Being Moderated
    William:

    You are probably correct, in that Oracle would, if they changed the behaviour, do it as a changed implementation of VARCHAR rather thant VARCHAR2. However, that statement about possible changing of the behaviour of VARCHAR has been around since at least 8.0 and probably earlier, so I tend to forget that they keep threatening to do so :-)

    John
  • 25. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    > Doug has it absolutely correct "Which is right? The first database you work with.
    Which are wrong? The other databases you need to support to make your application "database
    independent".", although for wrong, I would just say the other databases.

    This is not generally true. Even in most cases it's not true. At least not in my case. The first database I worked with was dBASE III/IV. I didn't like it and threw it away very soon. The second database was Fox Pro, much better than dBASE III/IV, and I used to embed its code in the Clipper (compiler tool) applications. Since FoxPro was too mickey-mouse-like, I came to the third database - Clarion Database Developer (database programming language, non-SQL, and rapid app dev tool with underlying flat-file database). BTW, Clarion was and today is the most intelligent developmnet tool that human brain ever manufactured in the software industry. At the same time I was working with Canadian Sterling ANSWER:Zim relational database which was the fourth database I worked with. The fifth database was Sybase SQL Server (later renamed to Sybase-ASE) sharing the same base code with MS SQL Server (the sixth database). From that point on I have been working with Sybase ASE on UNIXes and MS SQL Server on Windows platform. Last three years I have also actively worked with open source MySQL on Linux platform (the seventh database). Besides, I have basic knowledge of IBM DB2 and PostgreSQL. My favorite is, of course, Sybase ASE 15 (on Linux platform) which is the best piece of database software I have ever worked with, exclusively because of its outstanding performance and technical merits.

    > "It would be nice if this forum could put a collective "feature/bug fixing request" to force
    Oracle to bring its exotic definition of NULL into line with the rest of the world" Even if this
    forum could, or would put in such a request, it will never be changed. There are millions of lines
    of code out there that would break big time.

    Yes, that's the point. Finally I've got correct answer. That's the point - Oracle's spaghetti code, millions of lines of code based on screwed up definition of NULL. Relict of history, back to the eighties when Larry was programmer.

    Thanks.

    Regards

    Albert
  • 26. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    > Finally I've got correct answer. That's the point - Oracle's spaghetti code, millions of lines of code based on screwed up definition of NULL.

    No, I think John was referring to the applications developed by Oracle's customers since Oracle 7.0 replaced the variable-length CHAR with VARCHAR2 in the early nineties. I don't see what that has to do with spaghetti code.
  • 27. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    I used the term "Spaghetti code" in a pejorative meaning "huge, millions of lines of code", not in its primary meaning as "bunch of control structures with exceptions, GOTOs etc.".

    Albert
  • 28. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    Yes I know what "spaghetti code" means. My point was that code, whether good or bad, has been developed by Oracle customers against the documented behaviour, and you cannot just go and break it because you want the null semantics to be a bit more like Sybase. There is no point in saying all code written against Oracle since 1991 is rubbish and code written against Sybase is better.

    On the other hand, if people have written code using VARCHAR and are relying on it staying the same as VARCHAR2 as it has done for the last 15 years, then one day they may get what they deserve.
  • 29. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    > My point was that code, whether good or bad, has been developed by Oracle customers
    against the documented behaviour, and you cannot just go and break it because you want the
    null semantics to be a bit more like Sybase.

    Agreed. That's right. I understand. Oracle cannot fix the wrong definition of NULL without breaking up tens of thousands of its customers' applications. BTW, there is no null semantics to be a bit more like Sybase (why Sybase?). There is only one null semantics in set theory and relational algebra, accepted by all relevant database vendors, except Oracle. For Sybase, 2 + 2 = 4. What if Oracle says that 2 + 2 = 5. Would you advocate this with the argument "... you cannot just go and break it because you want the arithmetic to be a bit more like Sybase."?

    > There is no point in saying all code written against Oracle since 1991 is rubbish and code
    written against Sybase is better.

    I didn't say that. You did say that. But again: why Sybase? I am not in love with Sybase. I only respect it.

    Cheers

    Albert
1 2 3 4 26 Previous Next