Announcement Announcement Module
No announcement yet.
Having problems calling StoredProcedure (function) Page Title Module
Move Remove Collapse
Conversation Detail Module
  • Filter
  • Time
  • Show
Clear All
new posts

  • Having problems calling StoredProcedure (function)

    I created a simple function to locate a records id using three other pieces of information. The function returns the found id or 0 if no data is found. I can provide the function code if anyone wants.

    I then created a StoredProcedure object with function property set to true and an SqlOutParameter of type Types.INTEGER. The code itself seems to be ok, no errors and it runs, BUT I keep getting the value 0. When I call the function from within Toad using "SELECT function_name(...) FROM DUAL", I get the proper value. I know this isn't an accurate test in troubleshooting the error since Spring probably initiates a different call. Does anyone know how to make the same call from Toad of SQL*Plus? I'm on Oracle 9i.

    I'd really appreciate any help on this topic. Thanks!

    protected class UnitIdLookupQuery
    	extends StoredProcedure
    	private final String className = this.getClass().getName().substring(
    		this.getClass().getName().lastIndexOf("$") + 1)
    		+ ":  ";
    	public UnitIdLookupQuery(DataSource dataSource)
    		// Initialize the StoredProcedure object
    		super(dataSource, LookupDAOConstants.FN_GET_UNT_ID_FROM_EXCH_ID);
    		// Declare all the input parameters
    		super.declareParameter(new SqlParameter(
    			LookupDAOConstants.PARAM_IN_EXCHANGE_NUMBER, Types.VARCHAR));
    		super.declareParameter(new SqlParameter(
    		super.declareParameter(new SqlParameter(
    			LookupDAOConstants.PARAM_IN_SEQUENCE_NUMBER, Types.VARCHAR));
    		// Declare all the output parameters
    			new SqlOutParameter(LookupDAOConstants.PARAM_OUT_RESULTS, 
    	public int execute(String exchangeNumber, String developerSpaceNumber, String sequenceNumber) 
    		int unitId = 0;
    		HashMap paramsMap = new HashMap(4);
    		Map outMap = null;
    		paramsMap.put(LookupDAOConstants.PARAM_IN_EXCHANGE_NUMBER, exchangeNumber);
    		paramsMap.put(LookupDAOConstants.PARAM_IN_DEVELOPER_SPACE_NUMBER, developerSpaceNumber);
    		paramsMap.put(LookupDAOConstants.PARAM_IN_SEQUENCE_NUMBER, sequenceNumber);
    		long startTime = System.currentTimeMillis();
    		outMap = super.execute(paramsMap);
    		long endTime = System.currentTimeMillis();
    		if (outMap != null && outMap.size() > 0) {
    			try {
    				Integer temp = (Integer) outMap.get(LookupDAOConstants.PARAM_OUT_RESULTS);
    				if (temp != null) {
    					unitId = temp.intValue();
    			} catch (NumberFormatException e) {
    				// noop
    		if (log.isDebugEnabled() == true) {
    			log.debug(LookupDAOImpl.this.className + className
    				+ "SQL:         " + super.getCallString());
    			log.debug(LookupDAOImpl.this.className + className
    				+ "Parameters:  " +
    				"exchangeNumber: " + exchangeNumber + ", " + 
    				"developerSpaceNumber: " + developerSpaceNumber + ", " +
    				"sequenceNumber: " + sequenceNumber);
    			log.debug(LookupDAOImpl.this.className + className
    				+ "Row Count:   " + ((outMap == null) ? "?" : DataTypeConverter.formatInt(outMap.size())) + " rows");
    			log.debug(LookupDAOImpl.this.className + className
    				+ "Exec Time:   " + (endTime - startTime) + " ms.");
    		return unitId;

  • #2
    Are you sure that the return value is actually set? Or could it be that the the block setting it will be skipped?

    Besides that: As far as I know, TOAD uses the OCI interface while your application will most likely use the thin driver (I guess).

    Maybe you might try another JDBC driver and see if the problem lies there?



    • #3
      Try declaring your output parameter first.


      • #4
        Try declaring your output parameter first.
        Yes, that's how it works - declare the parameters in a left-to-right fashion. For a function that means the return value is declared first as an out parameter. Think of it the way you would construct the call in straight jdbc - {? = call function(?, ?, ?)}. Also, an IN_OUT parameter is declared as an out parameter and you also supply a value for it in the input parameter map.


        • #5
          That's It!

          Thank you. That was it... I needed to declare the output parameter before the input. It makes somewhat sense when you think about the syntax of the call "? = function(?, ?, ....)". BUT then I think about the fact that you do a declareParameter( new SqlOutputParameter(...) ) Couldn't it get the appropriate one when building the csc? Then again, I guess it's this way because it's code shared with stored procedure and you can have multiple I/O parameters.

          Oh well. Thanks again for the help. It was much appreciated.