OraFAQ Forum: SQL & PL/SQL » Data length of a column

Home » SQL & PL/SQL » SQL & PL/SQL » Data length of a column

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Data length of a column [message #31316]

Wed, 23 June 2004 02:34

Milly
Messages: 72
Registered: March 2004

Member

Hi all!

I have to retrieve the data lenght of a column...like the Datalength function in sqlserver...

Ex:

SELECT DATALENGTH(col1)
FROM mytable;

any idea?

�thanks!!

Report message to a moderator

Re: Data length of a column [message #31318 is a reply to message #31316]

Wed, 23 June 2004 02:54

Maaher
Messages: 7065
Registered: December 2001

Senior Member

1. DESCRIBE the table in SQL*Plus:

SQL> desc employee
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPLOYEE_ID                     NOT NULL NUMBER(4)
 LAST_NAME                                VARCHAR2(15)
 FIRST_NAME                               VARCHAR2(15)
 MIDDLE_INITIAL                           VARCHAR2(1)
 JOB_ID                                   NUMBER(3)
 MANAGER_ID                               NUMBER(4)
 HIRE_DATE                                DATE
 SALARY                                   NUMBER(7,2)
 COMMISSION                               NUMBER(7,2)
 DEPARTMENT_ID                            NUMBER(2)

Or you could query the dictionary (system tables):

SQL> SELECT column_name
  2       , data_type
  3       , data_length
  4       , data_precision
  5    FROM user_tab_columns
  6   WHERE table_name = 'EMPLOYEE';

COLUMN_NAME                    DATA_TYPE            DATA_LENGTH DATA_PRECISION
------------------------------ -------------------- ----------- --------------
EMPLOYEE_ID                    NUMBER                        22              4
LAST_NAME                      VARCHAR2                      15
FIRST_NAME                     VARCHAR2                      15
MIDDLE_INITIAL                 VARCHAR2                       1
JOB_ID                         NUMBER                        22              3
MANAGER_ID                     NUMBER                        22              4
HIRE_DATE                      DATE                           7
SALARY                         NUMBER                        22              7
COMMISSION                     NUMBER                        22              7
DEPARTMENT_ID                  NUMBER                        22              2

10 rows selected.

MHE

Report message to a moderator

Re: Data length of a column [message #31320 is a reply to message #31318]

Wed, 23 June 2004 03:10

Milly
Messages: 72
Registered: March 2004

Member

Yes...but for the column LONG the length is 0....

Report message to a moderator

Re: Data length of a column [message #31321 is a reply to message #31320]

Wed, 23 June 2004 03:47

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Correct, the table doesn't show BLOB, CLOB nor RAW lengths either. I assumed you meant the definition of a column instead of the actual length per tuple. For "normal" datatypes like number, there's the function LENGTH.

Try using CLOB columns in the future. There's a DBMS_LOB.GETLENGTH(CLOB) function. that performs your function quite nicely.

This is because they're special data_types. But if you want to find out the length of your long, here's something that might help you out: asktom.oracle.com. Search his site, you'll find lots of useful info there.

Good luck!

MHE

Report message to a moderator

Re: Data length of a column [message #31327 is a reply to message #31321]

Wed, 23 June 2004 05:06

Milly
Messages: 72
Registered: March 2004

Member

Unfortunately I Can't choose the datatype of the table...so...I'll search in this web site...and....

Thanks!

Report message to a moderator

Previous Topic:	query
Next Topic:	max count query joining 2 tables - how to?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 26 16:02:31 CDT 2026