October 18, 2010 at 12:06 pm
Where is CHAR datatype? 🙂
October 18, 2010 at 12:09 pm
the CHAR datatype was never part of my original post;
I was trying to find a setting or trick to get Oracle to treat NVL(SomeColumn,'') = '' to behave the same way SQL's ISNULL(someColumn,'') = ''
the suggestion of the CHAR data type is a tangent direction from the original issue. it doesn't apply to the question at hand, and of course I already know of the oracle behavior in both situations of char vs varchar2;
Lowell
October 18, 2010 at 12:42 pm
BowieRules! (10/18/2010)
Where is CHAR datatype? 🙂
Okay... this is going too far.
Would you agree RDBMS engine is the one who runs the show?
SQL> create table test(col1 char(10) not null);
Table created.
SQL> insert into test values('xxxxx');
1 row created.
SQL> insert into test values('');
insert into test values('')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPS$TEST"."TEST"."COL1")
SQL> insert into test values(Null);
insert into test values(Null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("OPS$TEST"."TEST"."COL1")
As you can see RDBMS engine treats both Null and '' as Null on a CHAR datatype column.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 20, 2011 at 6:58 am
PaulB-TheOneAndOnly and BowieRules!: Alright you kids, stop it. You're both right. 🙂
PaulB-TheOneAndOnly is correct in that "Oracle treats zero-length strings as Nulls" which btw was not the O.P.'s Q. It was "Is there an enviromental setting in Oracle that I am not aware of that might make an empty string treated as something other than NULL?" which "Jeff Moden" actually answered by saying "... and, no, there is no setting that I know of that will make it otherwise.".
BowieRules went above and beyond the O.P.'s Q. (which had already been answered "Jeff Moden") in suggesting a "workaround" to the inability by Oracle to distinguish b/w an Empty (aka 0-length) String and a Null Value. A "workaround" is obviously the next thing the O.P. would need given the answer to his Q. He may have not used the best language in phrasing his "workaround", but the point is he was trying to offer a "workaround", not disputing SSCrazy's statement. On any system that forces a value A (in this case an Empty String) to be treated as another value B (in this case Null), the obvious workaround would be to artificially designate another value C (i.e. a single space or fully-padded spaces) to represent value B. BTW, it could've been done with VarChar (as my company does), but Char would enforce at the DB vs. app level that noone accidentally tries to use value B vs. C. However, Char may have memory and usage disadvantages over VarChar.
Edited 12:28 pm: Fixed to use Posters' ID's vs. Member Level Names.
June 20, 2011 at 4:36 pm
tchien69 (6/20/2011)
PaulB-TheOneAndOnly and BowieRules!: Alright you kids, stop it. You're both right. 🙂
We stopped eight month ago!!! 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply