How do I get column datatype in Oracle with PL-SQL

Question

I have "read only" access to a few tables in an Oracle database. I need to get schema information on some of the columns. I'd like to use something analogous to MS SQL's sp_help.

I see the table I'm interested in listed in this query:

SELECT * FROM ALL_TABLES

When I run this query, Oracle tells me "table not found in schema", and yes the parameters are correct.

SELECT 
DBMS_METADATA.GET_DDL('TABLE', 'ITEM_COMMIT_AGG', 'INTAMPS') AS DDL
FROM DUAL;

After using my Oracle universal translator 9000 I've surmised this doesn't work because I don't have sufficient privileges. Given my constraints how can I get the datatype and data length of a column on a table I have read access to with a PL-SQL statement?

Answer 1

You can use the desc command.

desc MY_TABLE

This will give you the column names, whether null is valid, and the datatype (and length if applicable)

Answer 2

select column_name, data_type || '(' || data_length || ')' as datatype
from all_tab_columns 
where TABLE_NAME = upper('myTableName')

Answer 3

ALL_TAB_COLUMNS should be queryable from PL/SQL. DESC is a SQL*Plus command.

SQL> desc all_tab_columns;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                               NOT NULL VARCHAR2(30)
 DATA_TYPE                                          VARCHAR2(106)
 DATA_TYPE_MOD                                      VARCHAR2(3)
 DATA_TYPE_OWNER                                    VARCHAR2(30)
 DATA_LENGTH                               NOT NULL NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 NULLABLE                                           VARCHAR2(1)
 COLUMN_ID                                          NUMBER
 DEFAULT_LENGTH                                     NUMBER
 DATA_DEFAULT                                       LONG
 NUM_DISTINCT                                       NUMBER
 LOW_VALUE                                          RAW(32)
 HIGH_VALUE                                         RAW(32)
 DENSITY                                            NUMBER
 NUM_NULLS                                          NUMBER
 NUM_BUCKETS                                        NUMBER
 LAST_ANALYZED                                      DATE
 SAMPLE_SIZE                                        NUMBER
 CHARACTER_SET_NAME                                 VARCHAR2(44)
 CHAR_COL_DECL_LENGTH                               NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 AVG_COL_LEN                                        NUMBER
 CHAR_LENGTH                                        NUMBER
 CHAR_USED                                          VARCHAR2(1)
 V80_FMT_IMAGE                                      VARCHAR2(3)
 DATA_UPGRADED                                      VARCHAR2(3)
 HISTOGRAM                                          VARCHAR2(15)

Answer 4

Quick and dirty way (e.g. to see how data is stored in oracle)

SQL> select dump(dummy) dump_dummy, dummy
     , dump(10) dump_ten
from dual

DUMP_DUMMY       DUMMY DUMP_TEN            
---------------- ----- --------------------
Typ=1 Len=1: 88  X     Typ=2 Len=2: 193,11 
1 row selected.

will show that dummy column in table sys.dual has typ=1 (varchar2), while 10 is Typ=2 (number).

Answer 5

Oracle: Get a list of the full datatype in your table:

select data_type || '(' || data_length || ')' 
from user_tab_columns where TABLE_NAME = 'YourTableName'

Answer 6

select t.data_type 
  from user_tab_columns t 
 where t.TABLE_NAME = 'xxx' 
   and t.COLUMN_NAME='aaa'