CODEWITHSUNDEEP
sqloracle-databaseoracle11g
George Hernando
George Hernando

Reputation: 2650

Unable to determine datatype of table column in Oracle

I want to be able to determine the datatype of a column in a table. The database is Oracle 11.

My query is as follows:

"select t.data_type from user_tab_columns t where t.TABLE_NAME = '" + table.upper() + "' and t.COLUMN_NAME = '" + column.upper() + "'"

But for some reason I am not finding the table_name listed in user_tabl_columns.

I see the table with this command:

desc hr.t2

But this command finds nothing:

select unique table_name from user_tab_columns where table_name='hr.t2';

I don't see it listed when I do the following either:

select unique table_name from from user_tab_columns;

I'm also not seeing it when I do the following:

select unique table_name from all_tab_columns;

Upvotes: 1

Views: 632

Answers (3)

David Faber
David Faber

Reputation: 12495

Oracle object names are typically in uppercase unless they're created surrounded by double quotes (e.g., hr."t2"). That's not the case here, since you can refer to the object without double quotes using desc. Since the object is referred to as hr.t2 this means that the owner/schema is HR and the object name is T2. To find out the data type of a column in T2, one would use:

SELECT data_type FROM all_tab_columns
 WHERE owner = 'HR'
   AND table_name = 'T2'
   AND column_name = '<whatever the column name is>';

Upvotes: 1

William Robertson
William Robertson

Reputation: 16001

When the linked article uses CREATE TABLE HR.T2, this creates a table named T2 in the HR schema, not a table named "HR.T2". You would need to look for it in HR:

select data_type from all_tab_columns
where  owner = 'HR'
and    table_name = 'T2'

However, since the ALL_ views show you only what you have access to via ownership or grants, it's possible that you still won't be able to see it. Has HR given you permission to query it? The following shows what HR has granted to your user:

select * from user_tab_privs_recd
where  grantor = 'HR';

Or, connect as HR and query the privileges that have have been granted to other users:

select * from user_tab_privs_made;

Or, if you have been granted access to the DBA views:

select * from dba_tab_privs p
where  p.grantor = 'HR';

If this is just a demo, then it would be simplest to create the test tables in the same account that your application is connecting to (though this is not advisable in a real system for security reasons). This could be HR or some other schema.

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65408

Connect to HR, and then Query

select table_name 
  from user_tables 
 where table_name='T2';

If you see your table here, it means it exists, then query for columns of it as below :

select column_name 
  from user_tab_columns 
 where table_name='T2'; -- where "table name" should be  in upper case letters

If you can not see that table and you're sure there is a table called T2, then you may connect to SYS or SYSTEM schemas, and query as below :

select table_name 
  from dba_tables 
 where table_name='T2';

Upvotes: 1

Related Questions