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

[padma beerelli]

Hi,

I am new to spring and getting below exception

org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call USP_RETAIL_PKG.get_retail_stores(?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GET_RETAIL_STORES'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

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 = "USP_RETAIL_PKG.get_retail_stores";
	
	//  public GetRetailStoresProcedure(JdbcTemplate jdbcTemplate) {  
	 public GetRetailStoresProcedure(DataSource sldbdataSource) {
		  super(sldbdataSource, sp); 

		  System.out.println("proc getRetailStoresProcedure");
		      //   RowMapper rowMapper = new storeDetailsMapper(); 
		     //    setFunction(true);
		     //    declareParameter(new SqlReturnResultSet("out_records",rowMapper)); 
		     //   declareParameter(new SqlOutParameter("out_records",OracleTypes.CURSOR,rowMapper)); 
		         declareParameter(new SqlReturnResultSet("out_records",storeDetailsMapper)); 
		         declareParameter(new SqlParameter("in_flag",OracleTypes.VARCHAR)); 
		         declareParameter(new SqlParameter("in_value",OracleTypes.VARCHAR)); 
		         declareParameter(new SqlParameter("in_status", OracleTypes.VARCHAR)); 
		              
		   
		         // now compile stored proc 
		         compile(); 

		  	  }
	  
	  
	  @SuppressWarnings({ "unchecked", "rawtypes" })
   public Map getStoreDetails(String inFlag, String inValue, String inStatus) { 
	
			System.out.println("proc getStoreDetails........");
	        // set the input params 
			 
	        Map inParameters = new HashMap(); 
	       inParameters.put("in_flag", inFlag); 
	        inParameters.put("in_value", inValue); 
	       inParameters.put("in_status",inStatus); 
	    //    inParameters.put("out_records",rowMapper);
	        // now execute 
	    	System.out.println("proc  execute getStoreDetails........");
	        Map out = execute(inParameters); // [color=red][b] getting exception here[/b][/color]
	        if(!out.isEmpty()){
	        	System.out.println("proc out  :" + out);
	        }
	        
	            
	       
	        return out; 
	    } 
	    

	  
	  @SuppressWarnings("unused")
	 // private static final class  storeDetailsMapper implements RowMapper<GetRetailStores> {
	  private RowMapper<GetRetailStores> storeDetailsMapper = new 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 from Data Access Object

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

[padma beerelli]

and here is stored procedure

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

[Mark Spritzler]

Does the order of setting parameter types in the constructor important?

You have the out parameter first, but in the procedure it is the last parameter.

I haven't used the StoredProcedure class before, so this is a guess here.

Mark

[padma beerelli]

Thank you for reply.

I changed as per your suggestion but didnot work.

[Mark Spritzler]

Wait, I didn't notice this at first, but in your error

bad SQL grammar [{call USP_RETAIL_PKG.get_retail_stores(?, ?, ?)}]

There are three question marks there. Wouldn't there need to be 4?

I would assume that would be passing in the out paramter. Is that how it works?

Not sure, just something I noticed.

Mark