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

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
Symptoms
Cause
Solution

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.