Problem with USER_TAB_COLUMNS DATA_LENGTH

From: <bikle_at_bikle.com>
Date: 18 Jun 1999 19:30:59 GMT
Message-ID: <7ke6pj$ouq$1@samba.rahul.net>

Hi,

Consider and run the script below:

CREATE TABLE DROPME (N NUMBER (9,2)); SELECT DATA_LENGTH FROM USER_TAB_COLUMNS WHERE TABLE_NAME='DROPME'
AND COLUMN_NAME='N'; You will see this:

Table created.

DATA_LENGTH

---

         22

Obviously, DATA_LENGTH does not contain the correct length of the column we are interested in.

I poked around a bit and cooked up a decode which returns the correct length for this situation:

SELECT
DECODE(DATA_TYPE,
'CHAR', DATA_LENGTH||'',
'VARCHAR', DATA_LENGTH||'',
'VARCHAR2',DATA_LENGTH||'',
'NUMBER', NVL(DATA_PRECISION, 38)||','||NVL(DATA_SCALE, 0),
'FLOAT' , NVL(DATA_PRECISION, 38)||','||NVL(DATA_SCALE, 0),
NULL) LENGTH_INFO
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME='DROPME'
AND COLUMN_NAME='N'; which returns this:

LENGTH_INFO

---

9,2

-Dan

---

Daniel B. Bikle/Independent Oracle Consultant bikle_at_bikle.com | 650/941-6276 | P.O. BOX AG LOS ALTOS CA 94023 http://www.bikle.com

---

Received on Fri Jun 18 1999 - 14:30:59 CDT