org.springframework.jdbc.BadSqlGrammarException:CallableStatementCallback; bad SQL grammar

[padma beerelli]

Hi ,

I am new to Spring and getting below exception. Can anyone of you look at it.

28 Feb 2013 15:50:24,171 [[ACTIVE] ExecuteThread: '1' for queue: 'weblogic.kernel.Default (self-tuning)'] INFO  org.springframework.jdbc.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call sp123(?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'sp123'
ORA-06550: line 1, column 7

:

procedure sp123
( in_flag    in varchar2,
    in_value   in varchar2,
    in_status  in varchar2,
    out_records  out out_ref_cursor)

public class GetRetailStoresProcedure extends StoredProcedure {
                
                @Autowired
    @Qualifier("sldbdataSource")
                private DataSource sldbdataSource;
                
                @PostConstruct
                private void initialize() {
                                System.out.println("get  sldb connections for Procedure DAO");
                                setDataSource(sldbdataSource);
                }
                private static final String sp = "sp123";
                
                //  public GetRetailStoresProcedure(JdbcTemplate jdbcTemplate) {  
                 public GetRetailStoresProcedure(DataSource sldbdataSource) {
                                  super(sldbdataSource, sp); 

                                  System.out.println("proc getRetailStoresProcedure");
                                         RowMapper rowMapper = new storeDetailsMapper(); 
                                         declareParameter(new SqlReturnResultSet("out_records",rowMapper)); 
                                         declareParameter(new SqlParameter("in_flag",OracleTypes.VARCHAR)); 
                                         declareParameter(new SqlParameter("in_value",OracleTypes.VARCHAR)); 
                                         declareParameter(new SqlParameter("in_status", OracleTypes.VARCHAR)); 
                                              
                                            compile(); 

                                                  }
                  
                  
                  @SuppressWarnings({ "unchecked", "rawtypes" })
   public Map getStoreDetails(String inFlag, String inValue, String inStatus) { 
                
                                                System.out.println("proc getStoreDetails........");
                    
                                                 RowMapper rowMapper = new storeDetailsMapper(); 
                        Map inParameters = new HashMap(); 
                       inParameters.put("in_flag", inFlag); 
                        inParameters.put("in_value", inValue); 
                       inParameters.put("in_status",inStatus); 
                        inParameters.put("out_records",rowMapper);
                      
                                System.out.println("proc  execute getStoreDetails........");
                        Map out = execute(inParameters); 
                  
                        return out; 
                    } 

                  
                  @SuppressWarnings("unused")
                  private static final class  storeDetailsMapper implements RowMapper<GetRetailStores> {
                                                
                                                
//                                            @Override
                                                public GetRetailStores mapRow(ResultSet rs, int i) throws SQLException {
                                                                System.out.println("inner  maprow");
                                                
                                                        int  retailId=rs.getInt("retail_id");
                                                        String mlc=rs.getString("mlc");
                                                                                String storeName=rs.getString("store_name");
                                                                                String storeNumber=rs.getString("store_number");
                                                                                String area=rs.getString("area");
                                                                                String region=rs.getString("region");
                                                                                String city=rs.getString("city");
                                                                                String state=rs.getString("state");
                                                                                String phoneNumber=rs.getString("phone_number");
                                                                                String pendingStatus=rs.getString("pending_status");
                                                                                String  direct=rs.getString("direct");
                                                                                String disasterImpacted=rs.getString("disaster_impacted");
                                                                                
                                                                GetRetailStores retailStore = new GetRetailStores(retailId,mlc,storeName,storeNumber,area,
                                                                                region,city, state,phoneNumber,pendingStatus,direct,
                                                                                                disasterImpacted);
                                                                
                                                                return  retailStore;
                                                }
                                                
                                };
}

and calling from Data Access object.

 Map data = getRetailStoresProcedure.getStoreDetails(inFlag, inValue, inStatus);

[padma beerelli]

and here is stored procedure

PROCEDURE get_retail_stores (in_flag IN VARCHAR2,
in_value IN VARCHAR2,
in_status IN VARCHAR2,
out_records OUT out_ref_cursor);