My Oracle Support Banner

SqlException: Sqlstate(Null) Vendor Code (17110) Using DECODE And An Aggregate Fuction Like SUM With Nulls (Doc ID 391408.1)

Last updated on MARCH 08, 2017

Applies to:

JDBC - Version and later
Information in this document applies to any platform.


When attempting to use a SUM and DECODE functions in a select statement, it is generating the sql warning
on selecting 10 or more rows.

The select statement is as follows:

select code, SUM(DECODE(code, 1, value)) value
FROM codes
WHERE code <= 10

The warning generated is as follows:

java.sql.SQLWarning: Warning: execution completed with warning
at oracle.jdbc.dbaccess.DBError.addSqlWarning(
at oracle.jdbc.dbaccess.DBError.addSqlWarning(
at oracle.jdbc.dbaccess.DBError.addSqlWarning(
at oracle.jdbc.ttc7.TTC7Proto .doOall7(
at oracle.jdbc.ttc7.TTC7Proto .fetch(
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(
at org.hibernate.jdbc.AbstractBatcher.getResultSet(
at org.hibernate.loader.Loader.getResultSet(
at org.hibernate.loader.Loader.doQuery(
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(
at org.hibernate.loader.Loader.doList(
at org.hibernate.loader.Loader.list(
at org.hibernate.loader.hql.QueryLoader.list(
at org.hibernate.hql.ast.QueryTranslatorImpl.list(
at org.hibernate.impl.SessionImpl.list(
at org.hibernate.impl.QueryImpl.list(

The issue can be replicated as follows:

1. Create the database objects as follows:

drop table codes;
create table codes(
code number(2),
value number(3)

insert into codes values (1, 10);
insert into codes values (2, 20);
insert into codes values (3, 30);
insert into codes values (4, 40);
insert into codes values (5, 50);
insert into codes values (6, 60);
insert into codes values (7, 70);
insert into codes values (8, 80);
insert into codes values (9, 90);
insert into codes values (10, 100);
insert into codes values (11, 110);
insert into codes values (12, 120);
insert into codes values (13, 130);
insert into codes values (14, 140);
insert into codes values (15, 150);

2. Run the next sample code:

package testing;
import java.sql.*;

class SimpleSelect
  public static void main(String[] args)
   String url = "";
   String query = "select code, SUM(DECODE(code, 1, value)) value " + "FROM codes " +
                  "WHERE code <= 10 " + "GROUP BY code ";
     // Load the jdbc-odbc bridge driver

     // Attempt to connect to a driver. Each one
     // of the registered drivers will be loaded until
     // one is found that can process this URL
     Connection con = DriverManager.getConnection(url, "jdbc", "jdbc");

     // If we were unable to connect, an exception
     // would have been thrown. So, if we get here,
     // we are successfully connected to the URL

     // Check for, and display and warnings generated
     // by the connect.


     // Get the DatabaseMetaData object and display
     // some information about the connection

     DatabaseMetaData dma = con.getMetaData();

     System.out.println("\nConnected to " + dma.getURL());
     System.out.println("Driver " + dma.getDriverName());
     System.out.println("Version " + dma.getDriverVersion());

     // Create a Statement object so we can submit
     // SQL statements to the driver

     PreparedStatement stmt = con.prepareStatement(query);

     // Submit a query, creating a ResultSet object

     ResultSet rs = stmt.executeQuery();


     // Display all columns and rows from the result set


     // Close the result set


     // Close the statement


     // Close the connection

   catch (SQLException ex)
     // A SQLException was generated. Catch it and
     // display the error information. Note that there
     // could be multiple error objects chained
     // together

     System.out.println("\n*** SQLException caught ***\n");
     while (ex != null)
       System.out.println("SQLState: " + ex.getSQLState());
       System.out.println("Message: " + ex.getMessage());
       System.out.println("Vendor: " + ex.getErrorCode());
       ex = ex.getNextException();
   catch (java.lang.Exception ex)
     // Got some other type of exception. Dump it.


  // checkForWarning
  // Checks for and displays warnings. Returns true if a warning
  // existed

  private static boolean checkForWarning(SQLWarning warn)
  throws SQLException
    boolean rc = false;

    // If a SQLWarning object was given, display the
    // warning messages. Note that there could be
    // multiple warnings chained together

    if (warn != null)
      System.out.println("\n *** Warning ***\n");
      rc = true;
      while (warn != null)
        System.out.println("SQLState: " + warn.getSQLState());
        System.out.println("Message: " + warn.getMessage());
        System.out.println("Vendor: " + warn.getErrorCode());
        warn = warn.getNextWarning();
    return rc;

  // dispResultSet
  // Displays all columns and rows in the given result set

  private static void dispResultSet(ResultSet rs)
  throws SQLException
    int i;

    // Get the ResultSetMetaData. This will be used for
    // the column headings

    ResultSetMetaData rsmd = rs.getMetaData();

    // Get the number of columns in the result set

    int numCols = rsmd.getColumnCount();

    // Display column headings

    for (i = 1; i <= numCols; i++)
      if (i > 1)

    // Display data, fetching until end of the result set

    boolean more =;
    while (more)
      // Loop through each column, getting the
      // column data and displaying

      for (i = 1; i <= numCols; i++)
        if (i > 1)

      // Fetch the next result set row

      more =;


To view full details, sign in with your My Oracle Support account.

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

In this Document

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.