Oracle get column data size

I'm new to Oracle db, and i w开发者_StackOverflow社区onder if i can find out the datatype and datasize... I mean : let's say i have the column "location" of type varchar2(20) in table "products". Could i write a query that would return me in the result "varchar2" and "20" ?

Thank you!

Check out user_tab_columns.

select data_type, data_length 
  from user_tab_columns
 where table_name = 'PRODUCTS'
   and column_name = 'LOCATION';

Do you need to find out the type and size of a column for the purpose of understanding the data, or are you writing a program that needs to know it at runtime?

If you only need to know it for your knowledge, typing desc tablename in SQLPlus will tell you about the columns in the table.

If you need to find out the type and size programmatically, what language are you using ... PL/SQL? Java?

Note that selecting from USER_TAB_COLUMNS will only list the columns in tables in your own schema. For other tables you'll need to look at ALL_TAB_COLUMNS (or DBA_TAB_COLUMNS, if the DBA has allowed you to access it). Note that your employer or client may disallow stored procedures or packages from being compiled against either.

SELECT CHAR_LENGTH FROM USER_TAB_COLUMNS WHERE TABLE_NAME = <TABLENAME> AND COLUMN_NAME = <COLUMNNAME>;     

The answer to this question was useful to me. But, there is one important difference needs to be considered while taking data_length when the column type is NVARCAHR2. The data_length returned for NVARCHA2 column is doubled.

This is due to the character encoding being used. Oracle documentation says:

The number of bytes can be up to two times size for AL16UTF16 encoding and three times size for UTF8 encoding.

So, ideal query would be the following:

select table_name, column_name, 
data_type, NVL(char_col_decl_length, data_length) 
from user_tab_columns where table_name='T51_NOCOMP';

I do the following from sqlplus for oracle:

SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH 
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME = 'YOUR_TABLE_NAME'
AND COLUMN_NAME = 'YOUR';`

Hope it works for you too!