Re: oracle describe

"grasp06110" <grasp06110@yahoo.com> wrote in message
news:1152137979.244774.6300@m79g2000cwm.googlegroups.com...


I don't know if the Oracle describe _can_ be executed from Java; I've never
used Oracle so I have no idea.

However, I have worked with DB2 quite a bit and it also has a describe
command. Since the major database vendors generally all work use similar
standards, I assume that DB2's describe command has a similar function to
Oracle's, i.e. when used with a table, it tells you the names of the columns
in the table and gives you the datatypes and lengths of those columns.

Just for the heck of it, I tried doing a 'describe table' in DB2 via JDBC. I
was not surprised to find it didn't work. Here is the relevant source code:

        String queryTableSQL = "describe table rhino.department";

        /*
         * Get a description of the table.
         */
        Statement queryTableStmt = null;
        ResultSet rs01 = null;
        try {
            queryTableStmt = this.conn01.createStatement();
            rs01 = queryTableStmt.executeQuery(queryTableSQL);
        } catch (SQLException sql_excp) {
            System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered SQLException while describing table. Message: " + sql_excp);
            sql_excp.printStackTrace();
            System.exit(16);
        }

The error I got back from the JDBC driver said:

    executeQuery method cannot be used for update.

Basically, I'm pretty sure that executeQuery is expecting a SELECT
statement, not a DESCRIBE. Now, you could change the executeQuery() method
to execute() or executeUpdate() but then you won't get a result set back,
you'll only get an integer telling you how many rows were changed. However,
you're not changing rows and you want a result set so that you can see what
Describe has found for you.

In short, I doubt you'll be able to execute the Describe command via JDBC.
You'll need to do your Describe command from your Oracle command line or
whatever GUI Oracle gives you. Assuming, of course, that Oracle is
engineered roughly the same as DB2 is, with respect to Describe! For all I
know, Oracle gives you some kind of extension to let you do a Describe via
Java.

The good news is that, if Oracle is designed like DB2, there is another way
to get the same information via JDBC!

In DB2, all of the database objects are defined in the DB2 Catalog. The
catalog contains definitions of every table, view, index, etc. and the
catalog itself is structured in the forms of tables and views. In DB2, you
can query the catalog tables and views just as if they were user tables and
views: that means you can do SELECTs against them. Since those SELECTs can
be executed via JDBC, you can simply write an appropriate query against the
DB2 Catalog and get the same information as Describe gave you.

Here is an example of the Java code I need to execute to get the name,
datatype, length and scale of each column in the RHINO.DEPARTMENT table,
which is an ordinary user table. This information is stored in
SYSCAT.COLUMNS, a view in the DB2 Catalog.

        System.out.println("\nGet table information: ");
        String queryTableSQL =
            "select colname, typename, length, scale " +
            "from syscat.columns " +
            "where tabschema = 'RHINO' and tabname = 'DEPARTMENT' " +
            "order by colno";

        /*
         * Get a description of the table.
         */
        Statement queryTableStmt = null;
        ResultSet rs01 = null;
        try {
            queryTableStmt = this.conn01.createStatement();
            rs01 = queryTableStmt.executeQuery(queryTableSQL);
        } catch (SQLException sql_excp) {
            System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered SQLException while trying to describe table. Message: " +
sql_excp);
            sql_excp.printStackTrace();
            System.exit(16);
        }

        /*
         * Print a title line above the result set. The static method pad()
is
         * used to align the column titles and underlines.
         */
        String spaces = " ";
        System.out.println(
                pad("COLNAME", ' ', 'T', 20) + spaces +
                pad("TYPENAME", ' ', 'T', 20) + spaces +
                pad("LENGTH", ' ', 'T', 6) + spaces +
                pad("SCALE", ' ', 'T', 5)
                );
        System.out.println(
                pad("-------", ' ', 'T', 20) + spaces +
                pad("--------", ' ', 'T', 20) + spaces +
                pad("------", ' ', 'T', 6) + spaces +
                pad("-----", ' ', 'T', 5)
                );

         /*
         * Print each line of the result set.
         */
        try {
            while (rs01.next()) {
                System.out.println(
                        pad(rs01.getString("COLNAME"), ' ', 'T', 20) +
spaces +
                        pad(rs01.getString("TYPENAME"), ' ', 'T', 20) +
spaces +
                        pad(Integer.toString(rs01.getInt("LENGTH")), ' ',
'L', 6) + spaces +
                        pad(Integer.toString(rs01.getInt("SCALE")), ' ',
'L', 5)
                        );
            }
        } catch (SQLException sql_excp) {
            System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered SQLException while reading product information. Message: " +
sql_excp);
            sql_excp.printStackTrace();
            System.exit(16);
        }

        /* Close the result set and dispose of the statement. */
        try {
            rs01.close();
            queryTableStmt.close();
        } catch (SQLException sql_excp) {
            System.err.println(this.CLASS_NAME + "." + METHOD_NAME + " -
Encountered SQLException while closing result set or closing statement.
Message: " + sql_excp);
            sql_excp.printStackTrace();
            System.exit(16);
        }
    }

Now, I don't know if Oracle has a similar catalog; if it doesn't, the
approach I just suggested isn't going to work.

--
Rhino