Representing NULL in binary

Question

In PL/SQL DATE datatype stores fixed length values. They are stored in the binary format but displayed as character in the default format.

Consider the following declaration:

v_regdate DATE;

Here v_regdate is not initialized , hence its value is NULL. Just wondering how NULL would be stored in the binary format.

I'm not sure your question makes sense. There is nothing to store for a null value. It isn't some magic value, it is *no* value. — Alex Poole, Jan 28 '15 at 16:09
Date is a fixed length datatype. Hence v_regdate variable needs to have some value in the memory which represents NULL. — davison, Jan 28 '15 at 16:15
That assume the internal/in-memory representation of the type is simply its value, rather it will be a structure of some kind with semantics or associated metadata that can indicate its holding a null value — Alex K., Jan 28 '15 at 16:18

score 4 · Answer 1 · answered Jan 28 '15 at 16:19

If the value of a given date (actually, any datatype) variable / column is null, then nothing is stored. Null is just shorthand for nothing, nada, zip (at least in terms of what data is stored).

You can see that from this test case:

create table test (col1 number, col2 date);

insert into test (col1) values (1);

insert into test (col1, col2) values (2, sysdate);

commit;

select col1,
       to_char(col2, 'dd/mm/yyyy hh:mi:ss') col2,
       dump(col1) col1_dump,
       dump(col2) col2_dump
from   test;


      COL1 COL2                COL1_DUMP            COL2_DUMP                               
---------- ------------------- -------------------- ----------------------------------------
         1                     Typ=2 Len=2: 193,2   NULL                                    
         2 28/01/2015 11:12:33 Typ=2 Len=2: 193,3   Typ=12 Len=7: 120,115,1,28,12,13,34     

drop table test;

Note the "NULL" reported by the dump of col2 for the null value; that means "there is nothing stored in this column for this row".

Alex Poole · Accepted Answer · 2015-01-28T16:44:44.667

2

In PL/SQL DATE datatype stores fixed length values ...

That's not quite right. A date has an internal representation using seven bytes, yes, and all actual dates have a length of seven bytes, if you want to look at it like that. You can see that in the dump() from @boneist's answer, so I won't repeat it.

But a null value doesn't have those seven bytes all set to something - it isn't a magic value if that's what you're looking for; it's not seven bytes of 0x00, for example. A variable that is null has a length of zero bytes.

edited Jan 28 '15 at 16:44

answered Jan 28 '15 at 16:27

Alex Poole

183,384
11
179
318

so the DATE being a fixed length datatype does not hold true when its NULL. – davison Jan 28 '15 at 16:31
3

@davison - why do you think it's a fixed-length data type? Maybe splitting hairs but I'm not sure where you've got that term from. All allowed dates are represented in seven bytes, but it's an internal representation - an implementation detail, if you like. And null is unfathomable... – Alex Poole Jan 28 '15 at 16:34
1

@AlexPoole I agree i might have misunderstood this line "Date data is stored in fixed-length fields of seven bytes each" . Thanks for clearing it up http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT413 – davison Jan 28 '15 at 17:11
1

@Alex - it's in [the documentation](http://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#i54330): "The size is fixed at 7 bytes" ....{Dammit, that'll teach me for not refreshing before posting a comment!} – Boneist Jan 28 '15 at 17:12
Yes, in at least two places apparently. But I think that's just slightly misleading - it's useful for interpreting a dump, if you're getting as far as seeing typ=12/13, although it would be even more useful if it [went into more detail](http://stackoverflow.com/a/13568348/266304). And a similar discussion applies to the various timestamp types. – Alex Poole Jan 28 '15 at 17:17

score 2 · Answer 3 · answered Jan 28 '15 at 18:08

2

Remember that Oracle does not define how a null might be stored for a given data type because the definition of null really is "NOT DEFINED". This is why, for example:

SELECT 1 FROM DUAL WHERE NULL = NULL;

returns nothing. You can't compare one "undefined" to another "undefined" and expect equivalency.

answered Jan 28 '15 at 18:08

Michael Broughton

4,045
14
12

You're very close to the truth, but as i understood, there is still a difference between null and undefined. NULL is just NULL, comparing NULL to NULL (As in your example), gives the result UNDEFINED, which is neither TRUE or FALSE. A more detailed explanation is here: http://www.psinke.nl/blog/hello-world/ – Non Plus Ultra Jan 28 '15 at 20:45

Representing NULL in binary

3 Answers3