This content has been marked as final. Show 385 replies
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.
For Albert - Seriously though, working with nulls inWell spoken!
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.
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: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.
* 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)
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.
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.
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 ONI 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 ONThe 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 22.214.171.124, that is very "primitive" compared with 15.0, is still in production. Keep in mind, however, that this "primitive" ASE 126.96.36.199 was good enough to run most of Wall Street during the 90's.
>> You are welcome. But it doesn't mean you are very
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.
> 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.
> So they've finally introduced MVCC or the like?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.
Writers don't block readers anymore?
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.
> 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?
>> 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?