1 2 3 4 5 6 Previous Next 385 Replies Latest reply: Aug 20, 2007 7:26 AM by Alessandro Rossi Go to original post RSS
      • 45. Re: Treatment of zero-length strings as NULLs?
        480544
        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_
          Is[b] Albert not in today?
          • 47. Re: Treatment of zero-length strings as NULLs?
            castorp
            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
              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
                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
                  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
                    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
                      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
                        >> 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
                          > 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
                            >
                            > 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
                              > 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
                                > 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
                                  > 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
                                    >> 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 Previous Next