How to do a "desc emp" from JDBC

If you ever try to do a "desc emp" from a JDBC program , that won't work as that's a SQL*Plus command only, but here is how to write an SQL statement to do that which can easily be converted into a JDBC program and used.

Firstly here is what we get when we describe the EMP table in SQL*Plus.

SCOTT@linux10g> desc emp;
 Name                                Null?    Type
 ----------------------------------- -------- ------------------------
 EMPNO                               NOT NULL NUMBER(4)
 ENAME                                        VARCHAR2(10)
 JOB                                          VARCHAR2(9)
 MGR                                          NUMBER(4)
 HIREDATE                                     DATE
 SAL                                          NUMBER(7,2)
 COMM                                         NUMBER(7,2)
 DEPTNO                                       NUMBER(2)

Now here is the SQL we can use to get the same output from an SQL query.

select
 column_name as "Name",
 decode(NULLABLE, 'Y', '', 'N', 'Not Null') as "Null",
 data_type||decode(data_type,
 'NUMBER', decode(DATA_PRECISION, NULL, '',
 '('||DATA_PRECISION||','||data_scale||')'),
 'VARCHAR2', '('||data_length||')',
 'CHAR', '('||data_length||')') as "Type"
from user_tab_columns
where table_name = 'EMP'

The output of that is as follows from the query above , identical to a "desc emp", or close enough, couple more decodes will sort out a few minors issues , but seems to work for most tables I tested it against.

Name                           Null     Type
------------------------------ -------- ------------------------------
EMPNO                          Not Null NUMBER(4,0)
ENAME                                   VARCHAR2(10)
JOB                                     VARCHAR2(9)
MGR                                     NUMBER(4,0)
HIREDATE                                DATE
SAL                                     NUMBER(7,2)
COMM                                    NUMBER(7,2)
DEPTNO                                  NUMBER(2,0)

So with a bind variable defined it would simply be as follows

select
 column_name as "Name",
 decode(NULLABLE, 'Y', '', 'N', 'Not Null') as "Null",
 data_type||decode(data_type,
 'NUMBER', decode(DATA_PRECISION, NULL, '',
 '('||DATA_PRECISION||','||data_scale||')'),
 'VARCHAR2', '('||data_length||')',
 'CHAR', '('||data_length||')') as "Type"
from user_tab_columns
where table_name = ?

And then easily be integrated into a JDBC program, I did this over the web using a JSP page, which lists all user tables and adds a DESCRIBE button next to the table name to get the output above when clicked.