Announcement Announcement Module
Collapse
No announcement yet.
SP function not working Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • SP function not working

    I have simple PL/SQL function
    Code:
    CREATE OR REPLACE FUNCTION RTS_OWNER.fx (p_amount integer,
                    p_to_currency varchar2,
                    p_from_currency varchar2 DEFAULT 'USD'
                   )
          RETURN integer
       IS
       BEGIN
    
         RETURN 100;
       END fx;
    /
    Here is how I am calling the PL/SQL function :
    Code:
    import java.math.BigDecimal;
    import java.sql.Types;
    import java.util.HashMap;
    import java.util.Map;
    
    import javax.sql.DataSource;
    
    import org.springframework.jdbc.core.SqlOutParameter;
    import org.springframework.jdbc.core.SqlParameter;
    import org.springframework.jdbc.object.StoredProcedure;
    
    public class ExchangeRateProcedure extends StoredProcedure {
    	
    	public ExchangeRateProcedure(DataSource ds,String name){		
    		super(ds, "RTS_OWNER.fx");
    		setFunction(true);
    		declareParameter(new SqlParameter("p_amount", Types.NUMERIC));
    		declareParameter(new SqlParameter("p_to_currency", Types.VARCHAR));
    		declareParameter(new SqlParameter("p_from_currency", Types.VARCHAR));
    		declareParameter(new SqlOutParameter("p_r_amount",Types.NUMERIC));
    		compile();				
    	}
    	
    	public void execute() {		
    		Map<String,Object> in = new HashMap<String, Object>();
    		String c="USD";
    		String d="EUR";
    	    in.put("p_amount", new BigDecimal("100"));
    		in.put("p_to_currency", c);
    		in.put("p_from_currency",d);			
    		Map<String,Object> out=execute(in);	
    		System.out.println((BigDecimal)out.get("p_r_amount"));
    		System.out.println("done");
    	}
    }
    When I execute the above , I see the below exception . Any Thoughts on the reason for the error ?
    Please help , Its urgent.

    org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{? = call RTS_OWNER.fx(?, ?, ?)}]; nested exception is java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 1

    at org.springframework.jdbc.support.SQLStateSQLExcept ionTranslator.doTranslate(SQLStateSQLExceptionTran slator.java:98)
Working...
X