Thursday, May 01, 2008

How to check datatype length

ySQL> desc birthdates;
Name Null? Type
----------------------------------------- -------- ----------------------------
CLIENT_ID NUMBER
BIRTHDATE DATE

SQL> select * from birthdates;

CLIENT_ID BIRTHDATE
---------- ---------
1 01-MAY-08
2 01-MAY-08

The DUMP function shows the datatype, length (number of bytes), and the actual byte values
for a particular element.

SQL> select dump(client_id) from birthdates;

DUMP(CLIENT_ID)
--------------------------------------------------------------------------------
Typ=2 Len=2: 193,2
Typ=2 Len=2: 193,3

SQL> select dump(birthdate) from birthdates;

DUMP(BIRTHDATE)
--------------------------------------------------------------------------------
Typ=12 Len=7: 120,108,5,1,14,3,2
Typ=12 Len=7: 120,108,5,1,14,3,30