File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Oracle describe table from jdbc Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCM Java EE 6 Enterprise Architect Exam Guide this week in the OCMJEA forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Oracle describe table from jdbc" Watch "Oracle describe table from jdbc" New topic
Author

Oracle describe table from jdbc

Venkat Nagam
Greenhorn

Joined: Jul 28, 2003
Posts: 10
Gurus,
I need to get Table Schema from JDBC. I tried using following, but no luck.
...
stmt = conn.createStatement();
ResultSet rs1 = stmt.executeQuery("desc mytable");
while ( rs1.next() ) { //line 36
System.out.println(rs1.getString(1));
}
Here is exception:
java.lang.NullPointerException
at createScripts.main(createScripts.java:36)
Let me know if any idea what going wrong here.
Thanks.
Vinod Chandana
Ranch Hand

Joined: Aug 26, 2003
Posts: 59
Hi Venkat,
Use ResultSetMetaData. That is a much better option. I think the statement might be a problem (if the connection is correct). Do let me know if this helps.
[LINK]
URL: http://java.sun.com/j2se/1.4.2/docs/api/java/sql/ResultSetMetaData.html
[\LINK]
Regards,
Vinod.
Originally posted by Venkat Nagam:
Gurus,
I need to get Table Schema from JDBC. I tried using following, but no luck.
...
stmt = conn.createStatement();
ResultSet rs1 = stmt.executeQuery("desc mytable");
while ( rs1.next() ) { //line 36
System.out.println(rs1.getString(1));
}
Here is exception:
java.lang.NullPointerException
at createScripts.main(createScripts.java:36)
Let me know if any idea what going wrong here.
Thanks.
Venkat Nagam
Greenhorn

Joined: Jul 28, 2003
Posts: 10
Doesn't work...:-(
I added following line before like 36
ResultSetMetaData rsmd = rs1.getMetaData();
Now exception says there is problem in above statement.
java.lang.NullPointerException
at createScripts.main(createScripts.java:35)
Venkat Nagam
Greenhorn

Joined: Jul 28, 2003
Posts: 10
Found alternate solution...
select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE from USER_TAB_COLUMNS where TABLE_NAME='mytable' order by column_id;
Above query produces pretty much same output as "desc mytable".
Jay Dellinger
Greenhorn

Joined: Sep 19, 2002
Posts: 12
Hi Venkat,
ResultSetMetaData will give you meta data about the resultset from your query. If you want information on all the columns in your table, just run a select query to grab all the fields. I don't think it even needs to actually retrieve data, so send something like this:
"select * from table_name where 0 = 1"
Then get your ResultSetMetaData and it should have some useful information for you. There may be a different (better?) query you could run, but that is the basic idea.
Jay
Amit Da
Greenhorn

Joined: Mar 07, 2003
Posts: 16
Actually the ideal interface to be used here is DatabaseMetaData. You can get an object of its type from connection object.
Call method getColumns() on it. You need to pass following values
1.catalog - for Oracle pass null - because Oracle tables are not within a catalog - its a concept like package - for getting parameters of a stored proc, you'd have to pass package name here as you can have stored proc in a package.
2.schema = username value that owns table
3.tableName = exact table name or a pattern like MYTAB% (all tables starting with MYTAB)
4.columnName = if you need only certain columns matching a pattern pas pattern - e.g. COL% - all columns whose names start with COL
if you want all columns pass null or %
This getColumns() method does same thing as your "select COLUMN_NAME, DATA_LENGTH, DATA_TYPE from user_tab_columns" query (internally driver might be calling same). In short this class and its methods like getColumns() are very useful and you should use them as much as you can...
http://java.sun.com/j2se/1.3/docs/api/java/sql/DatabaseMetaData.html
Amit
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

Venkat,
According to the information you have supplied, "rs1" is null. (That's why you are getting a "NullPointerException" ;-) But I don't understand why, since, when I run your code on SUN Solaris 7 with Oracle 8i (8.1.7.4) and J2SE 1.3.1_02 and Oracle's "thin" JDBC driver, this line (of your code):

throws the following exception:

So I'm guessing that you haven't really supplied all of your code (or you're using a totally different environment to mine). So which is it? So perhaps if you supply the missing information, I may be able to help you further.
Good Luck,
Avi.
Sainudheen Mydeen
Ranch Hand

Joined: Aug 18, 2003
Posts: 218
Hi
"desc mytable" is a sql plus command. I am not sure we can use that here to get a ResultSet.

----------------
Sainudheen
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Originally posted by Sainudheen Mydeen:
Hi
"desc mytable" is a sql plus command. I am not sure we can use that here to get a ResultSet.

----------------
Sainudheen

The above is not a SQL statement so cannot be used by JDBC.


Groovy
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Use the following method of DatabaseMetaData
public ResultSet getTables(String catalog,
String schemaPattern,
String tableNamePattern,
String[] types)
throws SQLException
[ October 07, 2003: Message edited by: Pradeep Bhat ]
Amit Da
Greenhorn

Joined: Mar 07, 2003
Posts: 16
Actually getTables() method of DatabaseMetaData gives info about a table or tables matching pattern specified. But in this case since the user needs information about columns getColumns() method is more suitable.
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Originally posted by da Am:
Actually getTables() method of DatabaseMetaData gives info about a table or tables matching pattern specified. But in this case since the user needs information about columns getColumns() method is more suitable.

You are right.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1107

Sainudheen Mydeen wrote:Hi
"desc mytable" is a sql plus command. I am not sure we can use that here to get a ResultSet.

----------------
Sainudheen


I was getting very irate with squirrel SQL when it wouldn't run "desc mytable" till somebody explained that fact to me,
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Oracle describe table from jdbc