HELP! Significant problems with ORCLE's JDBC drivers

From: Axel Richrath <arichrath_at_verdi-consult.de>
Date: 2000/05/18
Message-ID: <3924029E.B4990378@verdi-consult.de>

Hello,

recently i passed the following request to ORACLE but unfortunately never got a reply. If somene knows solutions for one or both of the below described problems i shall be very happy to get a reply to mailto:arichrath_at_verdi-consult.de.

here is the copy of my request to ORACLE:

dear ladies and gentlemen,

the following two problems occurred while using oracle's jdbc thin driver version 8.0.5.0.0, whereby the database to be accessed is installed on a windows nt machine. it is the database, that was created during the installation process. no changes have been made to the database besides the fact, that a new schema 'DARO' and a user 'DARO' plus several tables for this schema have been created.

1. problems while retrieving column information using the DatabaseMetaData.getColumns() method:

several tables have been created using logicworx' erwin 3.5. these tables contain several columns that have been defined as being of type NUMBER rsp. INTEGER (both without any size attributes).

an example ddl would look like this:

create table DUMMY (VALUE1 NUMBER, VALUE2 INTEGER, VALUE3 NUMBER(8,2))

in all cases the sql worksheet's describe table (desc) command states, that the respective columns are defined as being of type NUMBER(38):

VALUE1 NUMBER(38)
VALUE2 NUMBER(38)
VALUE3 NUMBER(8,2)

issuing the DatabaseMetaData.getColumns() method brings up the follwing column definitions:

VALUE1 NUMBER(22)
VALUE2 NUMBER(22)
VALUE3 NUMBER(8,2)

to explain what we are doing, here is the respective code fragment, that retrieves the requested column iformation:

private void analyzeColumns (DARODbaReferenceGraph rg) throws Throwable
{
  ResultSet colSet = null;
  DARODbaInformationObject iobject = null;   DARODbaColumn column = null;

  for (Enumeration e = rg.getInformationObjects(); e.hasMoreElements(); )
  {
    if ((iobject = (DARODbaInformationObject)e.nextElement()) != null)     {
      if ((colSet = dbmd.getColumns (null, iobject.getParentSchema().getName(), iobject.getName(), null)) != null)

      {
        while (colSet.next ())
        {
          if ((column = rg.addColumn (new DARODbaColumn


(iobject,                  // where does the column belong to

                                      colSet.getString


(4),                        // the column's name

                                      colSet.getShort


(5),                         // the column's java/sql type

                                      (colSet.getInt (11) ==
dbmd.columnNullable), // can the column be NULL
                                      colSet.getString


(6),                        // the original type name

                                      colSet.getInt


(7),                           // the column's size/precision

                                      colSet.getInt


(9),                           // decimal digits

                                      colSet.getInt(17)))) !=
null)                // the ordinal position of the column
          {
            /* truncated code... */ ;
          }
        }
        colSet.close ();
      }

    }
  }
}

later on, the column defintion will be reverse engineered by the followixng code fragment of class DARODbaColumn:

public String getSqlDeclaration ()
{
  String sqlDeclaration = "";

  sqlDeclaration += name + " ";
  sqlDeclaration += sqlTypeName;

  switch (columnType)
  {
    /* handling of other types... */
    case java.sql.Types.INTEGER :
    case java.sql.Types.NUMERIC :
    {

      if (columnSize > 0)
      {
        sqlDeclaration += "(" + columnSize;
        if (decimalDigits > 0)
        {
          sqlDeclaration += "," + decimalDigits;
        }
        sqlDeclaration += ")";
      }
      break;

    }
    default :
    {
    }
  }
 return sqlDeclaration;
}

the obvious question is: why is the returned column size 22 instead of 38? did we miss something,
or is the jdbc driver buggy? is the algorithm implemented by the getSqlDeclaration method wrong? in any case, the COLUMN_SIZE value of the result set returned by DatabaseMetaData.getColumns() is 22.

2) strange exceptions while calling DatabaseMetaData.getIndexInfo()

a call to DatabaseMetaData.getTables() delivered - among others - the following table information:

TABLE_SCHEM = SYS
TABLE_NAME = _default_auditing_options_

the following call to DatabaseMetaData.getIndexInfo()

getIndexInfo (null, "SYS", "_default_auditing_options_", false, true)

throws an SQLException saying "ORA-00911: invalid character", whereas other schema/table-name combinations
are being executed successfully. the implied question is: why is that so? what does this error message mean? where is the invalid character probably located? is it a bug in the jdbc driver implementation? (assumably the latter, as this seems to be an error / exception raised by oracle itself)

we shall be very glad, if you could help us on these issues. furthermore we would be very interested to obtain information on where to get the newest versions of jdbc drivers espacially covering all the functionality introduced by the jdbc 2.0 specification.

with best regards,

Axel Richrath Received on Thu May 18 2000 - 00:00:00 CDT