JDBC ResultSetMetaData: Wrong Precision Value Returned for Column of Type Number Added by ALTER TABLE Statement (Doc ID 1266785.1)

Last updated on AUGUST 03, 2016

Applies to:

JDBC - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1   [Release: 11.1 to 11.2]
Information in this document applies to any platform.

Symptoms

Using JDBC ResultSetMetaData for finding the precision of a column defined as 'not null' will return a precision of 0.
This occurs when obtaining the metadata from a table that was altered by adding a new column of type NUMBER that has a default value and is not null.
For example, if you have the following database structure where the second column added is not null and has a default value.


create table foobar (a number(1) default 0 not null);
alter table foobar add (b number(1) default 0 not null);


running the code:

import java.sql.*;
import java.util.*;
import oracle.jdbc.*;

public class OracleMetaDataTestcase {
private final static String USER = "scott";
private final static String PASSWORD = "tiger";
private final static String TABLENAME = "foobar";

private final static String URL =
"jdbc:oracle:thin:@localhost:1521:orcl";

public static void main(String[] args) throws SQLException {
Properties properties = new Properties();
properties.setProperty("user", USER);
properties.setProperty("password", PASSWORD);

Driver driver = new OracleDriver();
Connection conn = driver.connect(URL, properties);
try {
Statement statement = conn.createStatement();
try {
createDDL(statement);
checkPrecision(statement);

// Lastly drop the table so it can be reran
statement.execute("drop table " + TABLENAME);
} finally {
statement.close();
}
conn.rollback();
} finally {
conn.close();
}
}

private static void createDDL(Statement statement) throws SQLException {
// Create the table here, note that the B column has a default of 0 and is not null
statement.execute("create table " + TABLENAME + " (a number(1) default 0 not null)");
statement.execute("alter table " + TABLENAME + " add (b number(1) default 0 not null)");
}

private static void checkPrecision(Statement statement) throws SQLException {
// We just do a simple select * from the table.
// This will return no rows and the metadata. This case is only about metadata
ResultSet res = statement.executeQuery("select * from " + TABLENAME);
try {
ResultSetMetaData meta = res.getMetaData();

int columnCount = meta.getColumnCount();
System.out.println("There are " + columnCount + " number of columns - expect 2");

for (int i = 0; i < columnCount; ++i) {
// This fails on the B column. Note it only happens on alter
// of the database table when adding a new column with a default
// and not null. If only one of each is done it works fine.
System.out.println("Column " + (i + 1) + " or " + meta.getColumnName(i + 1) +
" has precision of " + meta.getPrecision(i + 1) + " - expect 1");
}
} finally {
res.close();
}
}
}

will output the following:

There are 2 number of columns - expect 2
Column 1 or A has precision of 1 - expect 1
Column 2 or B has precision of 0 - expect 1

where Column B should return a precision of 1.

The problem does not occur if not using default value or null value.
This issue occurs using database version 11.1.0.6 and 11.2.0.1 (regardless of the JDBC driver version used).

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms