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

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?

Solution 1

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)

Solution 2

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)

Solution 3

The best solution that I've found for such case is

select column_name, data_type||
case
when data_precision is not null and nvl(data_scale,0)>0 then '('||data_precision||','||data_scale||')'
when data_precision is not null and nvl(data_scale,0)=0 then '('||data_precision||')'
when data_precision is null and data_scale is not null then '(*,'||data_scale||')'
when char_length>0 then '('||char_length|| case char_used 
                                                         when 'B' then ' Byte'
                                                         when 'C' then ' Char'
                                                         else null 
                                           end||')'
end||decode(nullable, 'N', ' NOT NULL')
from user_tab_columns
where table_name = 'TABLE_NAME'
and column_name = 'COLUMN_NAME';

@Aaron Stainback, thank you for correction!

Solution 4

Note: if you are trying to get this information for tables that are in a different SCHEMA use the all_tab_columns view, we have this problem as our Applications use a different SCHEMA for security purposes.

use the following:

EG:

SELECT
    data_length 
FROM
    all_tab_columns 
WHERE
    upper(table_name) = 'MY_TABLE_NAME' AND upper(column_name) = 'MY_COL_NAME'

Solution 5

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

View more solutions

Comments

[Aaron Stainback]

You need to add in CHAR_USED so you can tell if it was defined as byte or char in something like a varchar2

[Firas Nizam]

this is good code, but in case of INT field it returns "NUMBER(*,0)" It needs some correction

[sev3ryn]

@FirasNizam try it - maybe I don't understand you correctly but when data_scale is 0 it returns NUMBER(), when it is != 0 then it returns NUMBER(,data_scale)

[Tenzin]

@leanne You use this Table like this: SELECT * FROM user_tab_columns WHERE table_name = 'FILL_IN_THE_TABLE_NAME' AND column_name = 'FILL_IN_THE_COLUMN_NAME';

[leanne]

Yep, @Tenzin - multiple examples such as yours were provided here a couple of years ago, including the edit to this very post. If you click the 'edited ...' line in this answer, you'll see what Adam added to explain better. (Thanks, Adam!)

[AlexanderD]

Nice, for tables that are outside the current schema user_tab_columns can be replaced with all_tab_columns

[Michael Baarz]

The data_length is not important at all. The correct length definitions are defined with precision and scale.

[Michael Baarz]

That is completely what I have searched for. The separation in the cases are very important to get a correct type definition. Thank you for this!

[Sashi]

Welcome to stackoverflow. In addition to your code it is a good idea to also add a short text on why your answer is correct and inputs for the OP to correct whatever mistake they are facing.

[Anton Zaviriukhin]

There is a vsize function for that. It returns internal representation size of an expression.