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 but i'm having trouble doing so. 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" (Don't Oracles myriads of super intuitive response messages just make this so much fun... ), 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

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

Answer 3

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