Wanted: way to force a null (not NULL) value into a column.

[sampsonr]

I'm working (strictly as a knowledgable user) with an app vendor to extend their support to Oracle. To avoid major changes, they need a way to force a null value ('') into column since a lot of built-in SQL code uses expressions like "COLVAL = ''" rather than "COLVAL IS NULL". The first form apparently works with several other databases but not Oracle.

I tried using a before insert/update trigger to manipulate the incoming value but that didn't work. Can anybody offer any other suggestions?

 

[SantaMufasa]

Sampson,

Sorry for my lack of intuition on your question, but what are you REALLY asking for when you say, 'To avoid major changes, they need a way to force a null value ('') into column since a lot of built-in SQL code uses expressions like "COLVAL = ''" rather than "COLVAL IS NULL"'? Are you asking for 'a way to force a null value ('') into a column' or are you asking for a way to "fool" 'a lot of built-in SQL code (that) uses expressions like "COLVAL = ''" ' to behave as originally intended?

I'll tell you up front that you cannot "fool" Oracle into changing it's "never-TRUE" result from the conditional expression fragment, "...COLVAL = ''...".

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:58 (12Jan04) GMT, 12:58 (12Jan04) Mountain Time)

 

[carp]

Concur w/ Dave!
You can insert '' into a column in Oracle and it sets the value to NULL. But there is only one way to test for NULLness in a query (as far as I know!) - IS NULL (and its comical sidekick IS NOT NULL).

I think your apps developer is going to have to change their query if they want to work with Oracle!

The "Other" Dave
Elbert, CO
1308 MST

 

[sampsonr]

SantaMufasa,

Either solution would be satisfactory as long as it didn't require major application level changes to the existing SQL that could cause incompatibilities with other databases.

The app (Cerious software's ThumbsPlus) uses a SQL script to create the initial database that it connects to via ODBC. I'd like (hope?) to find a way to create the database in such a way that it could work with the existing app.

Both,

I suspected before posting the OP that this might not be possible but put it out here just in case.

 

[SantaMufasa]

Sampson,

The solutions are not really an "either/or" choice...they deal with two completely different issues:

Issue 1: If you wish "to force a null value ('') into column", then you can say (during INSERT, for example):
INSERT into <table-name> (<column-name-list>) VALUES
('some value',1234,NULL,'');

Notice that the last two column values &quot;force a null value&quot; into whatever are the last two columns in your <column-name-list>. So, either the (zero-argument function) NULL, and '' both achieve the same effect (i.e, record zero-length [lack of] data) in the receiving cell.

Issue 2: The fact that 'built-in SQL code uses expressions like &quot;COLVAL = ''&quot; rather than &quot;COLVAL IS NULL&quot; ' to test for NULLness. Sorry, you are out of luck on this one unless you (or apps developers) change the code.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:44 (12Jan04) GMT, 15:44 (12Jan04) Mountain Time)

 

[dsanchez2]

Possible ways around your problem:

(1) Pick a value that you would assign to your columns instead of NULL. For example:

INSERT into tab (name,age,dept)
values ('david',40,'-empty-');

instead of:

INSERT into tab (name,age,dept)
values ('david',40,NULL);

Then you will be checking for WHERE dept = '-empty-'

(2) You could use the nvl or decode function when checking for equality. For example:

WHERE nvl(dept,'-empty-') = '-empty-'

However the best solution is to use the IS NULL operator. This operator is in the SQL Standard, so it should be supported by most databases that use SQL.

 

[carp]

I think the problem with using &quot;secret code&quot; values here is that the queries have already been written. So using
'-empty-' is not going to help when the query is looking for &quot;where my_column = ''&quot;.

I would have to wonder how good the product is if it has been written using non-standard SQL! Have the vendors offered any explanation?

Elbert, CO
0842 MST

 

[sbix]

A side question for everybody ... I have an Oracle 7.3.4 DB, with a table containing DATE fields ... in some rows of this table, in the DATE fields sometime I find values which aren't DATE ... of any kind.
How is it possible???

 

[carp]

If the column is truly identified as a DATE datatype, I don't think it IS possible. Could you post some examples?

 

[sbix]

Yeah .. it's truly identified as DATE ... I have to check in the DB. The problem sometimes happens .... sometimes not

 

[sbix]

Here the problem:
DESCR TABLEXXX
XXX_KDOC NOT NULL VARCHAR2(16)
XXX_TIDOC_KDOC NOT NULL VARCHAR2(16)
XXX_NDOC VARCHAR2(10)
XXX_NPRE VARCHAR2(20)
XXX_DATA NOT NULL DATE

select xxx_data from table_xxx where xxx_data < '01-JAN-70';

xxx_data
---------
50-, -52
50-, -52
50-, -52
50-, -52
50-, -52
50-, -52
50-, -52
50-, -52
50-, -52
50-, -52

I can't explain these values

 

[carp]

Wow!
This one looks like a good candidate for a TAR! The only explanation I can think of is data corruption, but we don't want to think about that!

Elbert, CO
0835 MST

 

[SantaMufasa]

SBix,

Not only did you &quot;hitchhike&quot; on SAMPSONR's original thread, buy you then &quot;hijacked&quot; it and started &quot;off-roading&quot;, didn't you . (Good idea to start a new thread next time, okay?)

Now back to your issue: What happens when you execute this command:

&quot;analyze table TABLEXXX validate structure;&quot;

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:25 (14Jan04) GMT, 11:25 (14Jan04) Mountain Time)

 

[sbix]

I will try to use this thread as a bomb against some tablespaces ....
I know it's not matter of laugh ... but it was stronger than me