CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user2238790
user2238790

Reputation: 33

Finding data type with query

I need to find which fields are of CLOB data type in table with SQL query? I had tried the below query to fetch the data type but it is giving me error:

ORA-00942: table or view does not exist

Please suggest!!!

SELECT  data_type
FROM    SYS.COLUMNS
WHERE   OBJECT_ID = OBJECT_ID('PS_P1_EPA_EMPLOYEE');

Upvotes: 0

Views: 55

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

The data dictionary for Oracle isn't the same as for other RDBMS. If this is your own schema:

select data_type
from user_tab_columns
where column_name = 'PS_P1_EPA_EMPLOYEE'

... although that looks more like a table name, so maybe:

select column_name, data_type
from user_tab_columns
where table_name = 'PS_P1_EPA_EMPLOYEE'

You can also restrict on data_type ='CLOB'.

If it isn't in your schema, you can look in all_tab_colmns or dba_tab_columns. Documentation for all three views is here.

Upvotes: 4

Related Questions