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.
