Wednesday, August 26, 2009

Eclipse DTP vs Oracle PL/SQL - DESC is not SQL!

I have tripped up on this a few times. The Data Tools Project in Eclipse is excellent for SQL work, but against an Oracle database it runs SQL only, not PL/SQL.

A couple of times now I have tried to run "DESCRIBE SOME_TABLE" in an SQL file within Eclipse and gotten ORA-00900: invalid SQL statement (0 rows affected). This is because under Oracle, DESC[RIBE] is PL/SQL, not SQL.

Here is the SQL equivalent.

SELECT column_name "Name", nullable "Null?",
   concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM user_tab_columns
WHERE table_name = 'SOME_TABLE';