This discussion is archived
1 2 3 4 5 6 26 Previous Next 385 Replies Latest reply: Aug 20, 2007 5:26 AM by Alessandro Rossi Go to original post RSS
  • 45. Re: Treatment of zero-length strings as NULLs?
    480544 Newbie
    Currently Being Moderated
    Mu / Wu - Will, where do you find this stuff? :)

    The campaign for Mu inclusion starts here!

    For Albert - Seriously though, working with nulls in Oracle and any other RDBMS is something you come up against pretty quick. There's no point moaning about it or saying a different RDBMS is better or worse. It's just something you have to learn how it's been implemented and code appropriately.

    As others have lovingly pointed out other RDBMS have their flaws too, and everyone just codes around them. If it really bothers you that much, stop using oracle because one thing you can bet on is that it won't be changed anytime soon.
  • 46. Re: Treatment of zero-length strings as NULLs?
    Ash_ Newbie
    Currently Being Moderated
    Is[b] Albert not in today?
  • 47. Re: Treatment of zero-length strings as NULLs?
    castorp Explorer
    Currently Being Moderated
    For Albert - Seriously though, working with nulls in
    Oracle and any other RDBMS is something you come up
    against pretty quick. There's no point moaning about
    it or saying a different RDBMS is better or worse.
    It's just something you have to learn how it's been
    implemented and code appropriately.
    Well spoken!
  • 48. Re: Treatment of zero-length strings as NULLs?
    Tony Andrews Explorer
    Currently Being Moderated
    That said, differentiating between an empty string and NULL in relational calculus is nowhere specified in ANSI SQL.
    According to the Oracle SQL Reference it is:
    Oracle partially supports these subfeatures:

        * E021-02, CHARACTER VARYING data type (Oracle does not distinguish a zero-length VARCHAR string from NULL)
        * E021-03, Character literals (Oracle regards the zero-length literal '' as being null)
    I think Scott's position on this is the closest to my own: I wish Oracle treated '' and NULL as distinct for the sake of consistency, but I understand that the decision to treat them the same was made a long time ago (perhaps before there was an ANSI standard?) and probably will not (cannot) change any time soon.
  • 49. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    So far as I can tell, not having personally paid $155 to download a document written in confusing legalese, the actual ANSI standard regarding zero-length strings is vague and ambiguous and not much practical help.
  • 50. Re: Treatment of zero-length strings as NULLs?
    480544 Newbie
    Currently Being Moderated
    vague and ambiguous and not much practical help.
    And isn't that what it all comes down to? Personally I don't really give a rats *?£% what is theoretically correct as far as null's go. All that matters to me is that my code works, be it on oracle, sybase, sql server, java or any other language or RDBMS that has the concept of null. My employer's don't care if Oracle is theoretically correct - if my code breaks they don't want to hear me whinge about it being Oracle's fault - it's all about being practical.

    I can't believe I'm actually getting worked up about it... definitely time to go home. Have a good New Year, Will and everyone else out there.

    Graham
  • 51. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    Possibly.

    It is just as possible for database vendors to adopt a new query langage that does not involve NULLs.

    Alternatively, it is possible to build a SQL data model without NULLs and hence without the 3VL it implies.

    In Oracle also it now is worse with nested table columns since it is possible to have a NULL type as an element in a row/column, or an empty table as an element, or even a table with one (or more) NULL objects, or objects with null attributes. My lord save me.
  • 52. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    Hi Kamal,

    please note that Mr/Mrs/Miss "cd" was explicitly asking: "BTW: Did they finally manage to have a decent transaction model, or are dirty reads still an option?". Something like
    SET DIRTY READS ON
    I know what isolation levels mean in Sybase. In IBM DB2 too. It was unnecessarily to quote Trancast-SQL User's Guide. I've read it multiple times. Anyway, thanks for your efforts. ASE has four transaction isolation levels:

    * level 0 - read uncommitted, dirty reads are possible
    * level 1 - read committed (DEFAULT), allows shared read locking and prevents dirty reads
    * level 2 - repeatable read, prevents nonrepeatable reads
    * level 3 - serializable, prevents phantom rows (reads are valid until the end of all current transactions)

    Isolation levels are not only about dirty reads. They are much more than dealing with reads.

    I fail to see what could be wrong with the existence of these levels (similar to DB2). If you hesitate idea of dirty reads, don't switch to level 0 and just stay on level 1 (default level). If you don't want phantom rows, switch to level 3. Why to force consistency checking where the consistency is not in question (small environments, singe user systems and so on)? But if you want, force it anyway (with performance penalty, of course). I wish Oracle had something similar to switch between its "empty-string-nulls" to "ANSI" nulls, something like
    SET ANSINULLS ON
    The actual version of ASE is 15.0.1. But there are still 12.5.4/12.5.3/12.5.1/11.9.2 in production environments. Even the 11.0.3.3, that is very "primitive" compared with 15.0, is still in production. Keep in mind, however, that this "primitive" ASE 11.0.3.3 was good enough to run most of Wall Street during the 90's.

    Regards

    Albert
  • 53. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    >> You are welcome. But it doesn't mean you are very
    smart.
    Coming from you, this could still be a compliment.

    Don't be in doubt. It really was.


    > So they've finally introduced MVCC or the like? Writers don't block readers anymore?

    No, now readers block writers forever. It was Wall Street's feature request. Accept this information as my New Year's Gift to you.

    Albert
  • 54. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    > I didn't see anything in the section on "null (SQL)" about your requirement to be able
    to distinguish between "touched" and "non-touched" columns,...

    Of course, you didn't, for God's Sake! It was not an academic study from MIT. Just googled. The
    point was: instead of philosophizing about "conceptual problems" and "specific vendor
    implementations", it was enough to spend just a few seconds on Google to rebut that "philosophy".
    That's all. I don't recommend Google as resource for learning set theory.

    William, you really make me mad. Hope that you already worked with some app dev tool with
    smart IDE, screen object templates and code generator. "Touched" and "non-touched" terms
    apply primarily on application form fields, and then implicitly on underlying column. Any smart dev
    tool with a form painter, based on form templates or widgets, has some sort of automatic form
    control mechanism adjusted by developer (required/not required fields, defined set of allowed
    values for a field etc.). When you jump accross form fields pressing say the TAB key, no field will
    be "touched". When you press the SPACE key on a field, the field will be "touched" and
    empty-string will be inserted into the column in underlying table. If a field is not being touched and
    is marked as "not required", NULL will be inserted. That way, browsing the form later, or seeing
    rows as tabular array in a list/browse box, I can distinguish between "has not a phone_num"
    (empty-string -> blank field) and "phone_num unknown" (NULL -> "N/A" or "UNKNOWN" label on
    the field). That's trivial, but very elegant.

    Sorry, I cannot help you anymore.

    Regards

    Albert
  • 55. Re: Treatment of zero-length strings as NULLs?
    455796 Newbie
    Currently Being Moderated
    >
    > So they've finally introduced MVCC or the like?
    Writers don't block readers anymore?

    With MS SQL Server I thinks it's only been since 2005 that writers don't have to block readers. So in that sense they are catching up.

    But with Oracle? Still no temporary tables. I counted today 500 of our 1200 tables are temp. tables (ie. permanent 'temporary' tables). I didn't make them myself - but it looks like I'll have to clean them up. Sure, in Oracle you can have temporary data but the table is still permanent.

    And how about an identity column in Oracle? I tired of having to write a dumb trigger every time to implement a surrogate key.
  • 56. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    > Keep in mind, however, that this
    "primitive" ASE 11.0.3.3 was good enough to run most
    of Wall Street during the 90's.

    Maybe, but only to the extend that it ran.
  • 57. Re: Treatment of zero-length strings as NULLs?
    537882 Newbie
    Currently Being Moderated
    > But with Oracle? Still no temporary tables. I counted
    today 500 of our 1200 tables are temp. tables (ie.
    permanent 'temporary' tables). I didn't make them
    myself - but it looks like I'll have to clean them
    up. Sure, in Oracle you can have temporary data but
    the table is still permanent.

    My washing machine has a super quiet water efficient spin cycle which Oracle doesn't have.

    What's your point?

    To build good systems in Oracle you don't need "temporary tables" in sense that "temporary tables" exist in SQL Server.

    > And how about an identity column in Oracle? I tired
    of having to write a dumb trigger every time to
    implement a surrogate key.

    You don't have to write a trigger to implement a surrogate key, it very much depends how your transforms are externalised.

    Furthermore the helpful "identity columns" you refer to cause more problems than they solve. e.g. bcp out, bcp in...Odelay! what happen to my referential integrity?
  • 58. Re: Treatment of zero-length strings as NULLs?
    551707 Newbie
    Currently Being Moderated
    > Keep in mind, however, that this
    "primitive" ASE 11.0.3.3 was good enough to run
    most
    of Wall Street during the 90's.
    > Maybe, but only to the extend that it ran.

    I accept the extend: IT RAN. Today, 12.5.1/12.5.3/12.5.4/15.0 run.

    Albert
  • 59. Re: Treatment of zero-length strings as NULLs?
    William Robertson Oracle ACE
    Currently Being Moderated
    >> I didn't see anything in the section on "null (SQL)" about your requirement to be able
    to distinguish between "touched" and "non-touched" columns,...
    Of course, you didn't, for God's Sake!

    Then why quote it? Sorry but I don't see the point of the quote if it doesn't support your quite interesting argument that Oracle is in violation of some alleged standard.

    Yes I used to work with Oracle Forms, which has most of that stuff. This touching business never came up though. I suppose being an Oracle tool it wouldn't do. I'm still interested in what the tool would do about other datatypes, though. I suppose you could argue that the numeric equivalent is zero, but if it is so important to be able to say "I know for a fact that there is no phone number", why is nobody bothered about how to say "I know for a fact that there is no end date"? Or are they?
1 2 3 4 5 6 26 Previous Next