Toad World® Forums

Brain fry


#1

How do you display a column in hex - I have a Oracle column defined as as date but the vendor software is expecting nothing in the column.

Not sure if blank or null

Thanks


#2

Bill, not sure how to do the hex display, but have you tried querying the table

where youdatecolumn is not null?

That will at least tell you if the database thinks you have non-null values.

Best regards,
Mike

Michael S. Zarzycki, MTS | Manager of Engineering IT | Sensata Technologies,
Inc. | voice: 508-236-1015 | fax: 508-236-3701 | www.sensata.com | The World
Depends on Sensors and Controls


#3

As far as I know, if a date field in the database is empty, it is NULL. Blank is
not a value.
Groetjes,
Wim

On Mon, Feb 28, 2011 at 19:58, Bill Fry wrote:

How do you display a column in hex - I have a Oracle column defined as as
date but the vendor software is expecting nothing in the column.

Not sure if blank or null

Thanks

#4

If you want to know if it’s null, you can do a couple of things

1)Set the default value to show in grids for NULLs to something like

2)Use the nvl() function

For your date value, you’d need to convert it to text first…

select nvl(to_char( manager_id ), ‘BOSS - he works for no one!’ ) from HR .
EMPLOYEES

where manager_id is null;


#5

The column historically has the 1/1/1700 as the date, a new process is setting
it to null of which I need to fix our corrupted records. Looks like they need to
be set to null.

Thanks


#6

A date value cannot be blank? So NVL() is not needed. And beside showing
something like you could choose showing a color. My NULL fields are
yellow. Very clear if something is NULL or blank (for character fields that is).

Groetjes,
Wim

On Mon, Feb 28, 2011 at 20:10, Jeff Smith wrote:

If you want to know if it’s null, you can do a couple of things

 

1)Set the default value to show in grids for NULLs to something like

2)Use the nvl() function

 

For your date value, you’d need to convert it to text first…

 

select nvl(to_char( manager_id ), 'BOSS - he works for no one!' )
from HR . EMPLOYEES

where manager_id is null;

#7

How do you display a column in hex - I have a Oracle column defined as as
date but the vendor software is expecting nothing in the column.

Not sure if blank or null

If you’re wondering exactly what’s in the column, you can use the DUMP
function. e.g.:

SELECT my_date, DUMP(my_date) FROM my_table WHERE my_stuff = 1;

Granted, with dates you may want to add some columns, like:

dump(sysdate),dump(to_date(‘01/01/1900’,‘MM/DD/YYYY’))

Also, there is a known intentional bug where blanks (spaces?) can be
inserted into the 7-byte date column via OCI. It sucks. I wish Ora Corp
would fix it, if they haven’t already…

HTH! GL!

Rich – [TeamT]

Disclaimer: NFL Combine? I thought that was a farm implement.


#8

Also, there is a known intentional bug where blanks (spaces?) can be
inserted into the 7-byte date column via OCI. It sucks. I wish Ora Corp
would fix it, if they haven’t already…

I stand/sit/kneel corrected – it’s zero-filled and not spaces:

I don’t know if this still bug still “works”, but according to AskTom:

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2078184919103

…it’s no longer documented. And while not easily accessible, Oracle still
serves up 7.3 docs:

…which on page 3-15, displays the binary format of the DATE datatype,
which it says does not go through internal consistency checks.

Greeeeaaaat. Why would we want a silly thing like that on our DB?

Sorry for the tangent. Included for completeness. Or “complete mess”, as
it may be…

Enjoy!

Rich – [TeamT]

Disclaimer: Did you know your last name is an adverb?


#9

Evening Bill,

How do you display a column in hex
Select dump(column_name, 16) from table
where …

  • I have a Oracle column defined as
    as date but the vendor software is expecting nothing in the column.
    Can you define nothing? Do you mean NULL (ie, the complete and utter
    absence of a value) or a DATE of all zeros (which isn’t actually
    possible in Oracle - as far as I remember).

Not sure if blank or null
Select nvl(column_name, ‘It’‘s NULL’) from table
where …

However, blank or NULL could be synonymous - Oracle is a funny beast,
and empty string (two single quotes, nothing between) like ‘’ is
considered to be NULL. In other databases, this is NOT NULL but is a
zero length string.

The two are NOT the same, but Oracle treats both as NULL.

That’s why this never works:

Select stuff from table
where some_column = ‘’;

Even if the data inserted into some_column was indeed, the empty string.

HTH


Cheers,
Norm. [TeamT]


#10

Evening Wim,

A date value cannot be blank?
Yes it can:

Create table wim (a date);
Insert into wim (a) values (sysdate);
Insert into wim (a) values (NULL);
Commit;

(However, reading it again, I think I know what you are getting at - a
VALUE - hence not NULL - cannot be blank?)

So NVL() is not needed.
Well, it could be used to translate a NULL in to some valid date that is
known to the application. Much better to use NULLs than to fill in
default dates of, something like, ‘31/12/3999 00:00:00’ rather than use
NULL.

The former is good, the latter - the default date value in the table -
is bad as it fools the optimiser into thinking that there is a valid
range of dates in the data and can easily mess with cardinalities,
execution plans etc.


Cheers,
Norm. [TeamT]


#11

My understanding is this:

Any Oracle column/field can be set to null

The vendor can elect to set a date to a specific value such as in my case to
1/1/1700 to represent “No Date”

It is up to the vendor to code their software to reflect the right display upon
rendering the data to the UI.

In my case, I am afraid the vendor changed their internal architecture to shift
form a set value to null. If this is the case I am facing extreme pain to check
each form, program, etc to make sure the data is handled correctly. I am not
surmising the database triggers are bad as to the initialization. I have turned
the issue over to the vendor for validating the correct process.

Thanks


#12

from my understanding NULL is in an object with an uninitialised value
whereas ‘’ represents a 0 length string

most of the constructs i’ve seen test the null value with NVL(some_null_value)
if you can think of terms of OO design:
fubar is an parent object (which can be null)
|


#13

Evening Bill,

Any Oracle column/field can be set to null
This is correct - unless specifically defined as NOT NULL of course, or,
the column in question forms [part of] the primary key.

The vendor can elect to set a date to a specific value such as in my
case to 1/1/1700 to represent “No Date”
They can, and often do. In which case, performance can be badly affected
as the optimiser gets confused. It “thinks” that the so called null date
is actually a date - which it is - and calculates cardinalities etc
accordingly.

It is up to the vendor to code their software to reflect the right
display upon rendering the data to the UI.
It is, and I wish it happened more often. :wink:

In my case, I am afraid the vendor changed their internal architecture
to shift form a set value to null.
A good idea. But, causing you no end of grief it seems.

If this is the case I am facing
extreme pain to check each form, program, etc to make sure the data is
handled correctly.
Unless I’m missing the point, I would assume that the vendor has (a)
made the change to the “lack of date” handling and (b) has correctly
coded each form etc to deal with the changes. I’m unsure as to why you
are having all the grief?

I am not surmising the database triggers are bad as
to the initialization. I have turned the issue over to the vendor for
validating the correct process.
Seems to me that this is something that the vendor should have done in
the first place - they made the change, they need to fix the code.

Good luck.


Cheers,
Norm. [TeamT]


#14

Evening Martin,

from my understanding NULL is in an object with an uninitialised value
whereas ‘’ represents a 0 length string
Not quite. NUL is a complete and utter lack of a value. Uninitialised
means "I have not given this variable/object/whatever a defined value
(yet) so it takes it’s current value from the previous contents of the
memory in which it currently resides.

In C:

int Fred;
int Barney = 0;
int *Wilma = NULL;
int *Betty;

Only the pointer Wilma has a NULL value, Barney is zero, while Fred and
Betty could be anything. Wilma is explicitly saying “I have no value at
all”.

You might get away with attempting to dereference Betty - assuming that
the uninitialised value in there is in your processes own address (data)
space, otherwise an error. Dereference Wilma and you will (always) get
an address error.


Cheers,
Norm. [TeamT]


#15

The vendor can elect to set a date to a specific value such as in my
case to 1/1/1700 to represent “No Date”

It is up to the vendor to code their software to reflect the right
display upon rendering the data to the UI.

In my case, I am afraid the vendor changed their internal architecture
to shift form a set value to null. If this is the case I am facing
extreme pain to check each form, program, etc to make sure the data is
handled correctly. I am not surmising the database triggers are bad as
to the initialization. I have turned the issue over to the vendor for
validating the correct process.

Ew.

Ew. Ew. Ew. I think this is the first time in my four years here that I
do not have complete disdain for our vendor’s DB-agnostic storage of dates
in pseudo-Julian format in a numeric field.

Best of luck, Bill!

Rich – [TeamT]

Disclaimer: My father hung me on a hook once. Once!


#16

You could consider a before insert or update table trigger to prevent null from
going into the column.

Best regards,
Mike

Michael S. Zarzycki, MTS | Manager of Engineering IT | Sensata Technologies,
Inc. | voice: 508-236-1015 | fax: 508-236-3701 | www.sensata.com | The World
Depends on Sensors and Controls


#17

It’d be a lot cheaper to just add a DEFAULT value for that column in the
table definition? Although, I’d rather code my forms to understand NULL or
no date instead of screwing up the database with bad data.


#18

Hi Jeff,

It’d be a lot cheaper to just add a DEFAULT value for that column in the
table definition?
The vendor, according to Bill, changed from having a “not a date” date
value to using NULL.

Although, I’d rather code my forms to understand NULL
or no date instead of screwing up the database with bad data.
Ed Zachery!


Cheers,
Norm. [TeamT]


#19

Shakespeare already covered this tragedy in Much Ado About Nothing.