-
date type data length show at times 7 and at times 8
PL see the following I am not able to under stand
step1 --------------
select data_length from user_tab_columns where
data_type='DATE';
DATA_LENGTH
-----------
7
step2 --------------
create table xyz
(a date);
Table created
step3 --------------
insert into xyz values (to_date('10-aug-03'))
1 row created
step4 --------------
SQL> select distinct(data_length) from user_tab_columns where
2 data_type='DATE';
DATA_LENGTH
-----------
7
step5 --------------
create or replace view temp as (Select trunc(A) as a from xyz);
view created
step 6
SQL> select distinct(data_length) from user_tab_columns where
2 data_type='DATE';
DATA_LENGTH
-----------
7
8
step 7
select TABLE_NAME,COLUMN_NAME
from user_tab_columns where
data_type='DATE' and data_length=8;
The data length for date column shows 8 when we use truc(date_column) in a view..
As oracle uses 7 bytes to store a date type
usage
century year month date hour minute second
pls guide where has the length increased to 8..
regards,
Ashwani
-
The DATA_LENGTH column in USER_/ALL_/DBA_TAB_COLUMNS is more or less meaningless for views - in particular for DATE datatype. It doesn't tell you anything since view is only a stored query.
FYI, as soon as you perform any DATE operation on DATE datatype in view (apply any kind of DATE functions, perform any date arythmetics) the DATA_LENGTH will show 8 bytes - so it's not only with TRUNC() function.
Abyway, to understand where that 8th byte came from, check the following thread (in particular on page 2): http://www.dbasupport.com/forums/sho...threadid=16402
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|