Ok, I'm developing another oracle application where the distinction between NULL and '' will be important.
Given Oracle doesn't adhere to SQL standards for the distinction, is there any best practices for comparing, and storing blank strings in a table where the field is conceptually NOT NULL.
Thank you.
-- Pinging self [127.0.0.1] with 32 bites of banana cake:
Ping statistics for 127.0.0.1: Slices: Sent = 4, Received = 0, Lost = 4 (100% loss),
On Aug 17, 10:49 am, Matthew Harrison <m.harris...@craznar.com> wrote:
> Ok, I'm developing another oracle application where the distinction > between NULL and '' will be important.
> Given Oracle doesn't adhere to SQL standards for the distinction, is > there any best practices for comparing, and storing blank strings in a > table where the field is conceptually NOT NULL.
> Thank you.
> -- > Pinging self [127.0.0.1] with 32 bites of banana cake:
> Ping statistics for 127.0.0.1: > Slices: Sent = 4, Received = 0, Lost = 4 (100% loss),
I have always had difficulty with the concept that an empty string should not be considered a NULL value to begin with. What does an empty string hold? Oh wait, there is a binary zero used to indicate that the last user data value preceeds the binary zero. And if you compare two binary zeroes you get an equality so two empty strings are considered equal, but logically if the value is unknown there is no way determine if A = b, A < B, or A > B.
The string construct was a way for the developers of C to deal with the fact the processor they were using did not have instructions that delt with character data very well. Now we are apparently stuck with it.
For the most part when dealing with character data that could be unknown I have not found NULLs to be a problem even when the null had to be transferred into a string variable in a program. The database behaves by one set of rules and the program by another. If you need to treat nulls in a specific column as equal (emtpy strings) then you can use the NVL function in your where clause conditions, the is null and is not null conditions, and/or an additional attribute column to indicate empty where the column could also be NULL to handle the situation.
> Ok, I'm developing another oracle application where the distinction > between NULL and '' will be important.
I would be interested: if this is important for character columns, then I assume this is important for other columns as well. How do you implement that distinction for e.g. NUMBER or DATE columns?
Matthew Harrison wrote: > Ok, I'm developing another oracle application where the distinction > between NULL and '' will be important.
> Given Oracle doesn't adhere to SQL standards for the distinction, is > there any best practices for comparing, and storing blank strings in a > table where the field is conceptually NOT NULL.
Not unless you create a kludge. For example insert 'ZZYZX' where ever you intend an empty string.
The better choice would be, no matter the database product, to never have NULLs or empty strings. This can be done with proper design. -- Daniel A. Morgan University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
> Matthew Harrison wrote on 17.08.2007 16:49: >> Ok, I'm developing another oracle application where the distinction >> between NULL and '' will be important.
> I would be interested: if this is important for character columns, then > I assume this is important for other columns as well. > How do you implement that distinction for e.g. NUMBER or DATE columns?
If I get an assignment and I hand in an empty sheet that would be well empty. I typed zero characters. If I don't hand it in it is missing. It is not decidable whether my work is good or bad, long short.. there is the NULL. For numbers I think one can reasonably argue that 0 is the equivalent of empty. I don't think that there is a similar "empty" concept for dates.
Question: If empty strings make no sense for VARCHAR, why have them for CLOBs? AFAIK Oracle has helper functions to produce empty lobs.
Cheers Serge
PS: The Romans had some severe issues "getting" the concept of 0. Today we take it for granted. Perhaps '' is no different :-)
-- Serge Rielau DB2 Solutions Development IBM Toronto Lab
>> I would be interested: if this is important for character columns, >> then I assume this is important for other columns as well. >> How do you implement that distinction for e.g. NUMBER or DATE columns? > If I get an assignment and I hand in an empty sheet that would be well > empty. I typed zero characters. > If I don't hand it in it is missing. It is not decidable whether my work > is good or bad, long short.. there is the NULL.
If I don't hand it in, I don't "create a row in the database" that is something different compared to handing in an empty "row/assignment"
> For numbers I think one can reasonably argue that 0 is the equivalent of > empty.
Hmm. I get an an assignment where I should calculate something. I hand in a sheet where I put 0 (zero) as the solution. So that is the same as handing in an assignment where I didn't enter anything. Doesn't sound logical to me.
> I don't think that there is a similar "empty" concept for dates.
That's my point. Character seems to be the only data were everybody requires the distinction between "nothing" and "empty" but nobody has ever requested this distinction for dates or numbers.
> Question: If empty strings make no sense for VARCHAR, why have them for > CLOBs? AFAIK Oracle has helper functions to produce empty lobs.
I think that is a mere technical reason as for the CLOB some "management structures" (such as a locator) need to be created while this is not necessary with character data.
On Aug 17, 10:49 am, Matthew Harrison <m.harris...@craznar.com> wrote:
> Ok, I'm developing another oracle application where the distinction > between NULL and '' will be important.
> Given Oracle doesn't adhere to SQL standards for the distinction, is > there any best practices for comparing, and storing blank strings in a > table where the field is conceptually NOT NULL.
Having problems with "where the field is conceptually NOT NULL"?
Well it's either defined as NOT NULL in the database or it will allow nulls.
Why don't you define it as NOT NULL and remove the "is conceptually" out of the equation.
Providing a default value is also sometimes a good option.
> Serge Rielau wrote on 17.08.2007 19:38: >>> I would be interested: if this is important for character columns, >>> then I assume this is important for other columns as well. >>> How do you implement that distinction for e.g. NUMBER or DATE columns?
>> If I get an assignment and I hand in an empty sheet that would be well >> empty. I typed zero characters. >> If I don't hand it in it is missing. It is not decidable whether my >> work is good or bad, long short.. there is the NULL. > If I don't hand it in, I don't "create a row in the database" that is > something different compared to handing in an empty "row/assignment"
Depends on your data model doesn't it? What if the table looks like this:
STUDENT EXAM1 EXAM2 Bob 75 82 Tom 83 Kristi 94 92 Chick 56 47
So what does the entry for Tom in EXAM2 mean? There is a row in the table after all!
>> For numbers I think one can reasonably argue that 0 is the equivalent >> of empty. > Hmm. I get an an assignment where I should calculate something. I hand > in a sheet where I put 0 (zero) as the solution. So that is the same as > handing in an assignment where I didn't enter anything. > Doesn't sound logical to me.
I don't think that I'd ever equate 0 to empty for numbers. But maybe that's the mathematician in me. When computing averages or min values, a 0 can have a profound affect on the result where as no value can mean something else. Take for example the following set of numbers:
{0, 11, 22, 33, 44}
The average is 22 and the min is 0. Now take these numbers:
{NULL, 11, 22, 33, 44}
Now the average is 27.5 and the min is 11.
So for those reasons, I would not reasonably argue that 0 is the equivalent of empty. Of course...it does depend on how you are going to use this data. There are cases where it does not make any difference.
>> I don't think that there is a similar "empty" concept for dates. > That's my point. Character seems to be the only data were everybody > requires the distinction between "nothing" and "empty" but nobody has > ever requested this distinction for dates or numbers.
>> Question: If empty strings make no sense for VARCHAR, why have them >> for CLOBs? AFAIK Oracle has helper functions to produce empty lobs. > I think that is a mere technical reason as for the CLOB some "management > structures" (such as a locator) need to be created while this is not > necessary with character data.
>> I don't think that there is a similar "empty" concept for dates. > That's my point. Character seems to be the only data were everybody > requires the distinction between "nothing" and "empty" but nobody has ever > requested this distinction for dates or numbers.
The question of whether we choose to refer to some value as "empty" is entirely beside the point in my opinion. The issue is that the domain of string values supported by Oracle is not equivalent to the domain of string values supported by any other DBMS or programming language that I know of. Null is not a value. A string consisting of zero characters IS a value anywhere except Oracle.
You could "design out" the problem as Daniel Morgan suggests but then you may have to accept that some process or function which potentially has to support zero-length string values can no longer use Oracle as a data store. Alternatively, you have to compromise by using nulls to represent values or converting the strings to some other data type or representation.
> Matthew Harrison wrote: > > Ok, I'm developing another oracle application where the distinction > > between NULL and '' will be important.
> > Given Oracle doesn't adhere to SQL standards for the distinction, is > > there any best practices for comparing, and storing blank strings in a > > table where the field is conceptually NOT NULL.
> Not unless you create a kludge. For example insert 'ZZYZX' where ever > you intend an empty string.
> The better choice would be, no matter the database product, to never > have NULLs or empty strings. This can be done with proper design. > -- > Daniel A. Morgan > University of Washington > damor...@x.washington.edu (replace x with u to respond) > Puget Sound Oracle Users Groupwww.psoug.org
Proper design should reflect how the application being modeled works in the real world. In the real world information is often missing or unknown so a proper design will have nullable columns.
In Cost-Based Oracle Fundamentals Jonathin Lewis shows some examples of how using default values in place of nulls can mess up the CBO in its determination of cardinality, which is what other optimizer decisions are based on.
On Aug 17, 10:49 am, Matthew Harrison <m.harris...@craznar.com> wrote:
> Ok, I'm developing another oracle application where the distinction > between NULL and '' will be important.
> Given Oracle doesn't adhere to SQL standards for the distinction, is > there any best practices for comparing, and storing blank strings in a > table where the field is conceptually NOT NULL.
> Thank you.
> -- > Pinging self [127.0.0.1] with 32 bites of banana cake:
> Ping statistics for 127.0.0.1: > Slices: Sent = 4, Received = 0, Lost = 4 (100% loss),
I do not believe, one has to set default values or some other value in the field to treat as null. Any value you put in the field/variable is not same thing as null. One has to store information whether a value is null outside of field (such as in an indicator variable). Oracle took care of this long time ago. When one writes code, e.g., in PRO*C, Oracle has host variables and indicator variables. Host variable stores the actual value of the field and indicator variables store the fact whether the field is null or not. So when you want to save a null in database, you set indicator variable, e.g., to -1. When you get a value from Oracle it will set indicator variable to -1 if value was null. Any time you do a check in your code to find whether a value is null or not, you look at the indicator variable. Oracle provides similar concepts in other labguages such Java. In PL/SQL, internally Oracle perhaps uses same approach but Oracle has built short cuts in PL/SQL language. For example to find whether i is null, one writes code: If (i is null). To insert null, insert table (col1) values (null);
Any other mechanism used such as empty value for Strings , 0 or - 32767 for integer, or using default values are kludges which get you in trouble sooner or later because null is not same as any of these values. As long one follows Oracle's approach, there will not be any problems. In my view, Oracle's approach is the right approach.
Brian Peasland wrote: > STUDENT EXAM1 EXAM2 > Bob 75 82 > Tom 83 > Kristi 94 92 > Chick 56 47
> So what does the entry for Tom in EXAM2 mean? There is a row in the > table after all!
Correct. So if Tom just got everything wrong he gets a 0. If the TA hasn't reviewed the result yet that would be a NULL. Or perhaps Tom was sick and is going to do the exam later.
Now let's use: Firstname, Middlename, Lastname George Bush
Which one do I mean? If Middlename is an empty string then Sr. If Middlename is NULL it could be either. It's not specified.
There are plenty of debates about the usage of NULL in general in data bases.
You know me under 'Serge' NULL 'Rielau'. Homeland Security should not confuse that with 'Serge' '' 'Rielau' because that ain't me.
Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
> Brian Peasland wrote: > > STUDENT EXAM1 EXAM2 > > Bob 75 82 > > Tom 83 > > Kristi 94 92 > > Chick 56 47
> > So what does the entry for Tom in EXAM2 mean? There is a row in the > > table after all!
> Correct. So if Tom just got everything wrong he gets a 0. > If the TA hasn't reviewed the result yet that would be a NULL. > Or perhaps Tom was sick and is going to do the exam later.
> Now let's use: > Firstname, Middlename, Lastname > George Bush
> Which one do I mean? > If Middlename is an empty string then Sr. > If Middlename is NULL it could be either. It's not specified.
> There are plenty of debates about the usage of NULL in general in data > bases.
> You know me under 'Serge' NULL 'Rielau'. Homeland Security should not > confuse that with 'Serge' '' 'Rielau' because that ain't me.
> Cheers > Serge > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab
Y'all got some of that there homeland security stuff up in Toronto?
On Aug 17, 7:02 pm, Serge Rielau <srie...@ca.ibm.com> wrote:
> hpuxrac wrote: > > Y'all got some of that there homeland security stuff up in Toronto?
> US customs is at the Toronto Airport.
> Cheers > Serge
> -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab
Unless the airport security staff is a whole lot smarter than the ones we see around here I wouldn't advise any discussion about middle names being NULL or NOT NULL.
zigzag...@yahoo.com wrote: > On Aug 17, 5:23 pm, "David Portas" > <REMOVE_BEFORE_REPLYING_dpor...@acm.org> wrote: >> <zigzag...@yahoo.com> wrote in message
>>> On Aug 17, 10:49 am, Matthew Harrison <m.harris...@craznar.com> wrote: >>> Any value you put in the field/variable is >>> not same thing as null. >> Agreed. Any value is not the same thing as null
>>> In my view, Oracle's approach is the right approach. >> Huh? But Oracle's approach is that the empty string value IS the same thing >> as null!
>> -- >> David Portas
> I was not aware of that, but I do not belive same applied to other > data types such as integer,
Then you would be incorrect: It does.
Though I wouldn't suggest inserting an empty string into a DATE column. -- Daniel A. Morgan University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
> >>> On Aug 17, 10:49 am, Matthew Harrison <m.harris...@craznar.com> wrote: > >>> Any value you put in the field/variable is > >>> not same thing as null. > >> Agreed. Any value is not the same thing as null
> >>> In my view, Oracle's approach is the right approach. > >> Huh? But Oracle's approach is that the empty string value IS the same thing > >> as null!
> >> -- > >> David Portas
> > I was not aware of that, but I do not belive same applied to other > > data types such as integer,
> Then you would be incorrect: It does.
> Though I wouldn't suggest inserting an empty string into a DATE column. > -- > Daniel A. Morgan > University of Washington > damor...@x.washington.edu (replace x with u to respond) > Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
> - Show quoted text -
Why did then Oracle developed host variables, indicator variables etc. Keeping null indicator separate from the actual value is the right approach. When I have to insert null in a column, either I do not use that column in insert or use indicator variable (in PRO*C) or null in PL/SQL. It has always worked for me. Oracle may have messed up things but if a disciplined approach by keeping null indicator separate from the actual value is followed all through the code, things will still work.
hpuxrac wrote: > On Aug 17, 7:02 pm, Serge Rielau <srie...@ca.ibm.com> wrote: >> hpuxrac wrote: >>> Y'all got some of that there homeland security stuff up in Toronto? >> US customs is at the Toronto Airport. > Unless the airport security staff is a whole lot smarter than the ones > we see around here I wouldn't advise any discussion about middle names > being NULL or NOT NULL.
Better not. That's why IBM sells MDM solutions ;-)
Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
Serge Rielau wrote: > There are plenty of debates about the usage of NULL in general in data
In the case I'm working on the string is the command.
In their infinite wisdom, '' (the empty string) has a critical meaning .. I need to ensure that this string is specified (i.e. NOT NULL), but it can be specified as '' (meaning cancel).
-- Pinging self [127.0.0.1] with 32 bites of banana cake:
Ping statistics for 127.0.0.1: Slices: Sent = 4, Received = 0, Lost = 4 (100% loss),
zigzag...@yahoo.com wrote: >It has always worked for me. Oracle may have messed up > things but if a disciplined approach by keeping null indicator > separate from the actual value is followed all through the code, > things will still work.
So , I have a stored proc with a parameter MSG.
I have a table with values MSG and MSGISNULL.
How do I tell the parameter is '' or NULL ?
Or does the client user have to pass two parameters and do the test in their own langauge.
The catch here is that if it was a number, I just wouldn't put a value in ... that is ... the stored procedure would not have been called.
So - how do I detect, and act on someone NOT calling the stored procedure and set a flag ?
-- Pinging self [127.0.0.1] with 32 bites of banana cake:
Ping statistics for 127.0.0.1: Slices: Sent = 4, Received = 0, Lost = 4 (100% loss),
> zigzag...@yahoo.com wrote: > >It has always worked for me. Oracle may have messed up > > things but if a disciplined approach by keeping null indicator > > separate from the actual value is followed all through the code, > > things will still work.
> So , I have a stored proc with a parameter MSG.
> I have a table with values MSG and MSGISNULL.
> How do I tell the parameter is '' or NULL ?
> Or does the client user have to pass two parameters and do the test in > their own langauge.
> The catch here is that if it was a number, I just wouldn't put a value > in ... that is ... the stored procedure would not have been called.
> So - how do I detect, and act on someone NOT calling the stored > procedure and set a flag ?
> -- > Pinging self [127.0.0.1] with 32 bites of banana cake:
> Ping statistics for 127.0.0.1: > Slices: Sent = 4, Received = 0, Lost = 4 (100% loss),
Not sure I fully undesratnd , but I would think that client code has to pass two paremetrs, one indicating whethere value is null or not , and the other the actaul value. You may even consiser an indicator column in the table for this varchar2 column, so you can look at the value of the indicator column to decide whther " msg is not null.
zigzag...@yahoo.com wrote: > Not sure I fully undesratnd , but I would think that client code has > to pass two paremetrs, one indicating whethere value is null or not , > and the other the actaul value. You may even consiser an indicator > column in the table for this varchar2 column, so you can look at the > value of the indicator column to decide whther " msg is not null.
The point is - if the client is calling the procedure, then the message will not be null (but could be '').
That is - it's the uncalled messages are the problem ... I'd have to create a flag for all of them saying with a bit mask of null flags.
|
