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?

Answer 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)