DESCRIBE TABLENAME gives me : "java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement"

[Jason Smit]

I have tried the following SQL but it returned an error:
DESCRIBE DOGNAME;

error:
java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

I can run other SQL statements and they run without errors:
SELECT * FROM DOGNAME;

Why would this happen only for DESCRIBE?

[Martin Vashko]

DESCRIBE is an Sql*Plus command, not an SQL command. This means that it isn't executed by the database, but by the Sql*Plus client - the command line tool you can use to run both SQL and Sql*Plus commands and see their output on the console. It can be somewhat confusing to figure out which commands are pure SQL, and which are Sql*Plus ones - if it is the case, this page listing all Sql*Plus commands can help.

Note: some other tools, such as SqlDeveloper, can also execute the DESCRIBE command (as well as some other Sql*Plus commands). Similarly to the Sql*Plus client, they do it by interpreting the command themselves instead of sending them to the database server.

When you execute any Sql*Plus command through JDBC, the command is sent directly to the Oracle database, which doesn't know it - it isn't an SQL command as mentioned earlier. So you're getting the ORA-00900 exception.

If you want to query table structure from Java, you could use DatabaseMetaData. It probably isn't covered by Oracle's JDBC tutorial, but there are some examples available on the web. If you can't get it working, I'd suggest asking question in our JDBC forum, as this JDBC interface isn't Oracle specific.