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 9.2.0.3 and later
Information in this document applies to any platform.

Symptoms

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
GROUP BY code

The warning generated is as follows:

java.sql.SQLWarning: Warning: execution completed with warning
at oracle.jdbc.dbaccess.DBError.addSqlWarning(DBError.java:949)
at oracle.jdbc.dbaccess.DBError.addSqlWarning(DBError.java:1035)
at oracle.jdbc.dbaccess.DBError.addSqlWarning(DBError.java:1062)
at oracle.jdbc.ttc7.TTC7Proto .doOall7(TTC7Protocol.java:1899)
at oracle.jdbc.ttc7.TTC7Proto .fetch(TTC7Protocol.java:1199)
at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2512)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2840)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:608)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:536)
at
org.jboss.resource.adapter.jdbc.CachedPreparedStatement.executeQuery(CachedPreparedStatement.java:78
)
at
org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:
296)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
at org.hibernate.loader.Loader.doQuery(Loader.java:391)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
at org.hibernate.loader.Loader.doList(Loader.java:1593)
at org.hibernate.loader.Loader.list(Loader.java:1577)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74


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.net.URL;
import java.sql.*;

class SimpleSelect
{
  public static void main(String[] args)
  {
   String url = "jdbc:oracle:thin:@mhernand-us2.us.oracle.com:1521:V102020";
   String query = "select code, SUM(DECODE(code, 1, value)) value " + "FROM codes " +
                  "WHERE code <= 10 " + "GROUP BY code ";
   try
   {
     // Load the jdbc-odbc bridge driver
     Class.forName("oracle.jdbc.driver.OracleDriver");
     DriverManager.setLogStream(System.out);

     // 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.

     checkForWarning(con.getWarnings());

     // 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());
     System.out.println("");

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

     PreparedStatement stmt = con.prepareStatement(query);
     checkForWarning(stmt.getWarnings());

     // Submit a query, creating a ResultSet object

     ResultSet rs = stmt.executeQuery();

     checkForWarning(stmt.getWarnings());

     // Display all columns and rows from the result set

     dispResultSet(rs);

     // Close the result set

     rs.close();

     // Close the statement

     stmt.close();

     // Close the connection

     con.close();
   }
   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();
       System.out.println("");
     }
   }
   catch (java.lang.Exception ex)
   {
     // Got some other type of exception. Dump it.

     ex.printStackTrace();
   }
  }

  //-------------------------------------------------------------------
  // 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());
        System.out.println("");
        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)
      System.out.print(",");
      System.out.print(rsmd.getColumnLabel(i));
    }
    System.out.println("");

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

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

      for (i = 1; i <= numCols; i++)
      {
        if (i > 1)
        System.out.print(",");
        System.out.print(rs.getString(i));
      }
      System.out.println("");

      // Fetch the next result set row

      more = rs.next();
    }
  }
}

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