CallableStatement resultset returns only columns and no data, where data exists.

[Jyotsna Pai]

Hi there,

Can someone please help me with this one.
I am trying to retrieve results by invoking a stored procedure - APPCOMMON.MY_PROC.
This procedure has 3 IN parameters of type varchar and 1 OUT parameter of type SYS_REFCURSOR.

The concern is that when I hard-code the 3 above mentioned input parameters in my stored procedures, the java code below works fine and gives me output as a Collection - 'results' which I then use to display an excel sheet. This shows me a table with 88 columns and 39 records.

But when I try to pass the parameters like below -
callStmt.setString(1, district);
callStmt.setString(2, workGroup);
callStmt.setString(3, systemStatus);

I don't get any data from the backend. But I am able to retrieve the column-names into the Collection - results.
Seems like some problems retrieving resultset, but I am not able to identify it.

Please advise.

MyClass1.java - 
  private static final String qryString = "{call APPCOMMON.MY_PROC(?, ?, ?, ?)}"; // The stored Procedure to be invoked.
  String district = request.getParameter("district");     // returns - "CTH"
  String workGroup = request.getParameter("workGroup");   // returns - "IPWEW"
  String systemStatus = request.getParameter("systemStatus");  // returns - "U"
  MyClass2 myclass2 = new MyClass2(transactionContext);
  Collection results = MyClass2.invokeStoredProcedure(qryString, district, workGroup, systemStatus); 

MyClass2.java-
  public Collection invokeStoredProcedure(String sql, String district, String workGroup, String systemStatus) throws DataAccessException 
  {

    Collection results = new ArrayList();
    Connection conn = null;
    CallableStatement callStmt = null;
    ResultSet resultSet = null;
    String columnName = "";
    try {
      conn = transactionContext.getDBConnection();      // This returns a valid connection object to the database
      callStmt = conn.prepareCall(sql);                         // Prepare call with the given sql.      
      callStmt.setString(1, district);
      callStmt.setString(2, workGroup);
      callStmt.setString(3, systemStatus); 
      callStmt.registerOutParameter(4, OracleTypes.CURSOR); // Specify that the output type is a Oracle Type CURSOR. (The stored procedure's parameter is - SYS_REFCURSOR)
            
      callStmt.execute();    
      LOG.debug("MaintenanceQueryDAO: Stored Procedure: statement executed.");     
      
      resultSet = (ResultSet) callStmt.getObject(4);
      ResultSetMetaData rsmd = resultSet.getMetaData();
      int numberOfColumns = rsmd.getColumnCount();
      ArrayList row = new ArrayList();

      for (int i = 1 ; i <= numberOfColumns ; i++) {
         columnName = Utility.formatColumnName(rsmd.getColumnName(i).trim());
         LOG.debug("columnName: " + columnName);   
         row.add(columnName);
         LOG.debug("Column row: " + row);  // The log shows me all the columns retrieved successfully - 88 columns
      }
      results.add(row);
      LOG.debug("Column results: " + results);  
      
      while (resultSet.next()) {               // In debug mode this contains nothing.
          row = new ArrayList();
          for (int i = 1 ; i <= numberOfColumns ; i++) { 
             row.add(resultSet.getString(i));
             LOG.debug("Data row: " + row);  
          }
          results.add(row);
          LOG.debug("Data results: " + results);  
      }
    } catch (SQLException sqlException) {
      sqlException.printStackTrace();
    } finally {
      try {   
        callStmt.close();   
        resultSet.close();
        resultSet = null;        
      } catch (SQLException sqlException) {
        sqlException.printStackTrace();
      }  
    }
   return results;
}

The Stored procedure is -

    
CREATE OR REPLACE PROCEDURE MY_PROC
(
--
-- Primary Criteria
--
   vdstrct_code IN VARCHAR,
   vwork_group  IN VARCHAR,
   vtask_status IN VARCHAR,
   cv_results OUT SYS_REFCURSOR
   ) AS 
BEGIN ....
SELECT * FROM TABLE1
....

WHERE 
....
DSTRCT_CODE = vdstrct_code    // If I hard-code here, I get results with 88 columns and 39 rows.
AND WORK_GROUP = vwork_group
AND TASK_STATUS = vtask_status 
END;
/


CREATE OR REPLACE PACKAGE MY_TYPES_PKG
IS
   TYPE refCursor IS REF CURSOR;
END MY_TYPES_PKG;
/

The stored procedure has compiled successfully.

Please advise. Any help is much appreciated.

Thanks and Regards,
Jyotsna Pai

[Christophe Verré]

Are you sure the input values are correct ? Try to hard code the values in your Java source :

callStmt.setString(1, "CTH");  
callStmt.setString(2, "IPWEW");  
callStmt.setString(3, "U");

(or whatever the values are)

[Jyotsna Pai]

Hi Christopher,

Thanks for your reply.

I have tried hard-coding the setString exactly as you advised.
It did not help. I could again retrieve only the columns, but no data.

I also tried the below -

   
    Collection results = new ArrayList();
    Connection conn = null;
    CallableStatement callStmt = null;
    ResultSet resultSet = null;
    String columnName = "";
    try {
      conn = transactionContext.getDBConnection();      
      callStmt = conn.prepareCall(sql);  
      callStmt.setString("vdstrct_code", "CTH");
      callStmt.setString("vwork_group", "IPWEW");
      callStmt.setString("vtask_status", "U");
      callStmt.registerOutParameter("cv_results", OracleTypes.CURSOR); 
      callStmt.execute(); 

      resultSet = (ResultSet) callStmt.getObject("cv_results");
      ResultSetMetaData rsmd = resultSet.getMetaData();
      int numberOfColumns = rsmd.getColumnCount();
      ArrayList row = new ArrayList();

      for (int i = 1 ; i <= numberOfColumns ; i++) {
         columnName = Utility.formatColumnName(rsmd.getColumnName(i).trim());
         LOG.debug("columnName: " + columnName);   
         row.add(columnName);
         LOG.debug("Column row: " + row);  
      }
      results.add(row);
      LOG.debug("Column results: " + results);  
      
      while (resultSet.next()) { 
          row = new ArrayList();
          for (int i = 1 ; i <= numberOfColumns ; i++) { 
             row.add(resultSet.getString(i));
             LOG.debug("Data row: " + row);  
          }
          results.add(row);
          LOG.debug("Data results: " + results);  
      }
    } catch (SQLException sqlException) {
      sqlException.printStackTrace();
      String errorMsg = "MaintenanceQueryDAO: Stored Procedure: PL/SQL error " + sqlException;
      LOG.error(errorMsg);      
      generateDataAccessException("[**Error in invokeStoredProcedure **]\n", sql, sqlException);
    } finally {
      try {   
        callStmt.close();   
        resultSet.close();
        resultSet = null;        
      } catch (SQLException sqlException) {
        generateDataAccessException("[**Error in invokeStoredProcedure **]\n", sql, sqlException);
      }  
    }      
    return results;
  }

Doesn't work either.

[Jyotsna Pai]

Hello everybody,

To all those who went through this page, Thanks a lot!
I have finally managed to resolve this issue myself.
The issue was not with the java code, but was with the stored procedure.
I changed the stored procedure to include MY_TYPES_PKG.refCursor instead of SYS_REFCURSOR.-
The stored procedure now is -

    
CREATE OR REPLACE PROCEDURE MY_PROC   
(   
--   
-- Primary Criteria   
--   
   vdstrct_code IN VARCHAR,   
   vwork_group  IN VARCHAR,   
   vtask_status IN VARCHAR,   
   cv_results OUT MY_TYPES_PKG.refCursor
   ) AS    
BEGIN    
OPEN cv_results FOR
SELECT * FROM TABLE1   
....   
  
WHERE    
....   
DSTRCT_CODE = vdstrct_code    
AND WORK_GROUP = vwork_group   
AND TASK_STATUS = vtask_status    
END;   
/   

I created a package called MY_TYPES_PKG as follows -

    
CREATE OR REPLACE PACKAGE MY_TYPES_PKG AS
TYPE refCursor IS REF CURSOR;
END MY_TYPES_PKG;
/

I ensured to give GRANT to the user on both the package and the procedure.

And my code works fine.

For those of you, who would wish to see the code again, it is as follows -

    
MyClass1.java -    
  private static final String qryString = "{call APPCOMMON.MY_PROC(?, ?, ?, ?)}"; // The stored Procedure to be invoked.   
  String district = request.getParameter("district");     // returns - "CTH"   
  String workGroup = request.getParameter("workGroup");   // returns - "IPWEW"   
  String systemStatus = request.getParameter("systemStatus");  // returns - "U"   
  MyClass2 myclass2 = new MyClass2(transactionContext);   
  Collection results = MyClass2.invokeStoredProcedure(qryString, district, workGroup, systemStatus);    
  
MyClass2.java-   
  public Collection invokeStoredProcedure(String sql, String district, String workGroup, String systemStatus) throws DataAccessException    
  {   
  
    Collection results = new ArrayList();   
    Connection conn = null;   
    CallableStatement callStmt = null;   
    ResultSet resultSet = null;   
    String columnName = "";   
    try {   
      conn = transactionContext.getDBConnection();      // This returns a valid connection object to the database   
      callStmt = conn.prepareCall(sql);                         // Prepare call with the given sql.         
      callStmt.setString(1, district);   
      callStmt.setString(2, workGroup);   
      callStmt.setString(3, systemStatus);    
      callStmt.registerOutParameter(4, OracleTypes.CURSOR); // Specify that the output type is a Oracle Type CURSOR. (The stored procedure's parameter is no more SYS_REFCURSOR but is MY_TYPES_PKG.refCursor)   
               
      callStmt.execute();       
      LOG.debug("MaintenanceQueryDAO: Stored Procedure: statement executed.");        
         
      resultSet = (ResultSet) callStmt.getObject(4);   
      ResultSetMetaData rsmd = resultSet.getMetaData();   
      int numberOfColumns = rsmd.getColumnCount();   
      ArrayList row = new ArrayList();   
  
      for (int i = 1 ; i <= numberOfColumns ; i++) {   
         columnName = Utility.formatColumnName(rsmd.getColumnName(i).trim());   
         LOG.debug("columnName: " + columnName);      
         row.add(columnName);   
         LOG.debug("Column row: " + row);  // The log shows me all the columns retrieved successfully - 88 columns   
      }   
      results.add(row);   
      LOG.debug("Column results: " + results);     
         
      while (resultSet.next()) {               // This now contains 39 records.   
          row = new ArrayList();   
          for (int i = 1 ; i <= numberOfColumns ; i++) {    
             row.add(resultSet.getString(i));   
             LOG.debug("Data row: " + row);     
          }   
          results.add(row);   
          LOG.debug("Data results: " + results);     
      }   
    } catch (SQLException sqlException) {   
      sqlException.printStackTrace();   
    } finally {   
      try {      
        callStmt.close();      
        resultSet.close();   
        resultSet = null;           
      } catch (SQLException sqlException) {   
        sqlException.printStackTrace();   
      }     
    }   
   return results;   
} 

Thanks to everyone!

Cheers,
Jyotsna Pai

[Christophe Verré]

Good to hear it is solved. I must admit I had no clue what the problem could have been.

[Jyotsna Pai]

No worries Chris!

All we want to achieve is that the issue gets reoslved.
I am thankful to you for looking into it and helping me with it!

Thanks again!
Jyotsna Pai