Monday, 25 August 2008

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.

3 comments:

Anonymous said...

Thx! Awesome (just need "desc" for generated documents... thx!)

Nigel said...

Pas

You could also use JDBC's ResultSetMetadata:


Statement stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(
"SELECT * FROM " + tableName + " WHERE 1=0");

ResultSetMetaData md = rs.getMetaData();


Simply parsing the select query (which returns no rows) gets the definitions for you... see eg http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSetMetaData.html for more info

Regards Nigel

Pas Apicella said...

yep perfect