Oracle DB query data_length(what's it return)

Question

I am running some queries on an Oracle DB from my java program. Once a certain table is selected this query runs and returns some traits for the user.

A simplified version of my code as follows.

String query = "SELECT column_name, data_type, data_length FROM all_tab_columns WHERE table_name = 'blahblah'";
String ColumnName;
String DataType;
String DataLength;

System.out.println(ColumName);
System.out.println(DataType);
System.out.println(DataLength);

I am unsure as to what data_length is returning.

Is it returning the length of the data in the field? Or is it returning the max value. After some searching I have come across conflicting opinions. And when comparing the data type I get with its associated length it seems that it could be the max length but who knows maybe the field is just maxed out?

For Example:

Data Type = Number is returning Data Length = 22

Data Type = VARCHAR2 is returning Data Length = 2000

Bonus: (+5 points on final grade, and 1 free absence from class)

How could I change the query to get the data I dont have. (actual data length, or max data length).

Hahaha ouch. Despite my clever wording(which I found hilarious) of my own bonus question this is not homework. — sealz
– sealz, Commented Aug 11, 2011 at 15:23

Shannon Severance · Accepted Answer · 2011-08-11 17:47:17Z

8

It should be relatively easy to test. Note that there is no ALL_TABLE_COLUMNS view-- there is an ALL_TAB_COLUMNS and an ALL_TAB_COLS view that provide this information. I'll create an empty table and run the query I assume your professor intended.

SQL> create table empty_table (
  2    col1 number(10,2),
  3    col2 varchar2(100),
  4    col3 varchar2(100 byte),
  5    col4 varchar2(100 char)
  6  );

Table created.

SQL>   select column_name, data_type, data_length
  2      from all_tab_columns
  3     where table_name = 'EMPTY_TABLE'
  4  order by column_name;

COLUMN_NAME                    DATA_TYPE      DATA_LENGTH
------------------------------ -------------- -----------
COL1                           NUMBER                  22
COL2                           VARCHAR2               100
COL3                           VARCHAR2               100
COL4                           VARCHAR2               300

Now that you've seen the actual result, it should be clear what length is being returned in the DATA_LENGTH column.

edited Aug 11, 2011 at 17:47

Shannon Severance

18.5k4 gold badges49 silver badges67 bronze badges

answered Aug 11, 2011 at 15:20

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sealz Over a year ago

ahh correct, my actualy code uses all_tab I will edit. Guess I had a moment haha.

sealz Over a year ago

I think Ive got it, so just to be clear between the two answers, it is the maximum allowed amount the field can hold? Not the max amount it was created with?

Justin Cave Over a year ago

@harper89 - I'm not sure what you mean by "the max amount it was created with". ALL_TAB_COLUMNS tells you the declared maximum size the column was created with. It tells you nothing about the data that was inserted in that column after the table was created.

sealz Over a year ago

I was referring to the other answer. That is what I need then, Just need to know the max that is allowed not what is there already. Thanks

Randy · Accepted Answer · 2011-08-11 15:16:33Z

0

This should represent the length that column was created with.

i.e. numbers default to 22, but you may override that NUMBER(5) or something. varchar2 (4000) etc.

In order to find the actual max length of the values contained in the column, you need to query the table itself, not the data dictionary.

SELECT max( len( mycol )) from mytab;

there may be some statistics gathered for these things, but that may not be reliable for your application.

answered Aug 11, 2011 at 15:16

Randy

16.7k2 gold badges40 silver badges56 bronze badges

1 Comment

Shannon Severance Over a year ago

"numbers default to 22, but you may override that NUMBER(5)" Data length is not precision. create table t (n number(5)); select column_name, data_length from all_tab_columns where table_name = 'T'; gives back a data length of 22.

Collectives™ on Stack Overflow

Oracle DB query data_length(what's it return)

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related