How to get column type of column in Oracle with PL-SQL with low privileges?

Question

I have read-only access to only a few tables in an Oracle database. I need to get schema information for some columns. I would like to use something similar to MS SQL sp_help .

I see the table that interests me in the following query:

 SELECT * FROM ALL_TABLES 

When I run this query, Oracle tells me that "the table was not found in the 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 9000 universal translator, I assumed that this does not work because I do not have sufficient privileges. Given my limitations, how can I get the data type and data length in a column of a table that I have read access to using the PL-SQL statement?

Answer 1

ALL_TAB_COLUMNS must be requested 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 2

You can use the desc command.

 desc MY_TABLE 

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

Answer 3

Note. If you are trying to get this information for tables that are in another SCHEMA, use the all_tab_columns view, we have this problem, because our Applications use another 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' 

Answer 4

The best solution I have found for such a 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, thanks for the fix!

Answer 5

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

Answer 6

Oracle: get a list of the complete data type in your table:

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

Answer 7

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

Answer 8

A quick and dirty way (e.g. see how the 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 the dummy column in the sys.dual table has typ = 1 (varchar2), and 10 has Typ = 2 (number).