stored-proc-outbound-gateway & Oracle Stored Procedure with OUT SYS_REF_CURSOR

[dec_kelly]

Hi,

I'm trying to use the stored-proc-outbound-gateway to return results from an Oracle SP which has a spec similar to this.

PROCEDURE get_data
( out_csr                                 OUT SYS_REFCURSOR
, in_param                        IN     table.some_column%TYPE
);

The procedure is being called however I'm not getting results back, even when I try making the proc completely trivial where it just selects sysdate from dual.

The issue seems to be that when it gets to

JDBCTemplate.call(CallableStatementCreator csc, List<SqlParameter> declaredParameters)

After it calls execute and gets the return value and updateCount these are used to decide whether to extract results.

However since an Oracle Stored procedure doesn't return a result and CallableStatement.execute() defines it's return values as

Returns:
true if the first result is a ResultSet object; false if the first result is an update count or there is no result

I get return value of false and updateCount of -1 causing this check to evaluate to false and hence no results extracted.

if (retVal || updateCount != -1) {
	returnedResults.putAll(extractReturnedResults(cs, updateCountParameters, resultSetParameters, updateCount));
}

I've never actually tried to call a stored procedure via JDBCTemplate in this manner before instead using

org.springframework.jdbc.object.StoredProcedure

However in this case this option isn't available as it's all hidden away.

Can anyone advise of options please?

P.S.

StoredProcOutboundGateway.handleRequestMessage(Message<?> requestMessage)

returns a null in this case rather than sending any message at all. Is this the desired behaviour?

Thanks

[Cleric]

Hi!

Your config should be like this:

<jdbc:stored-proc-outbound-gateway data-source="dataSource"
                                                   ignore-column-meta-data="true"
		                                   stored-procedure-name="get_data">
			<jdbc:sql-parameter-definition name="in_param"/>

			<jdbc:returning-resultset name="OUT_CURSOR"/>
			<jdbc:parameter name="in_param" value="foo"/>
		</jdbc:stored-proc-outbound-gateway>

And also take a look into our samples catalog: https://github.com/SpringSource/spri...ration-samples

Take care,
Artem

[dec_kelly]

Hi,

Thanks for replying.

My config is already quite similar to that (I can't post the actual config due to company policies but here's the gist)

<int-jdbc:stored-proc-outbound-gateway data-source="dataSource"
                                                   stored-procedure-name="get_data"
                                                   request-channel="requestChannel"
                                                   reply-channel="replyChannel" 
                                                   ignore-column-meta-data="true"
                                                   >
			<int-jdbc:sql-parameter-definition name="in_param" direction="IN" type="DATE"/>
	                 <!-- -11 is the value of oracle.jdbc..driver.OracleTypes.CURSOR constant-->
   		        <int-jdbc:sql-parameter-definition name="out_param" direction="OUT" type="-11"/>
			<int-jdbc:parameter name="in_param" expression="payload"/>
</int-jdbc:stored-proc-outbound-gateway>

I have tried using

<int-jdbc:returning-resultset name="out_param"/>

instead of defining the OUT parameter, but then I get an ORA error about the wrong number of parameters or wrong parameter types, it's actually the types as it is setting 2 parameters as it should. When I debug through it I'm seeing the same issue mentioned in this post on StackOverflow.

http://stackoverflow.com/questions/1...ning-resultset

Am I missing something???

[Cleric]

Try this:

PACKAGE PKI_DATA
IS
  TYPE ref_cursor IS REF CURSOR;

  PROCEDURE get_data(
    out_data      OUT  ref_cursor,
    out_data_2   OUT  ref_cursor,
    in_count  IN   NUMBER

  );

<jdbc:stored-proc-outbound-gateway data-source="dataSource"
	ignore-column-meta-data="true"
	stored-procedure-name="PKI_DATA.GET_DATA">

	<jdbc:sql-parameter-definition name="OUT_DATA" type="#{T(oracle.jdbc.OracleTypes).CURSOR}" direction="OUT"/>
	<jdbc:sql-parameter-definition name="OUT_DATA_2" type="#{T(oracle.jdbc.OracleTypes).CURSOR}" direction="OUT"/>
	<jdbc:sql-parameter-definition name="IN_MAX_ROWS" type="NUMERIC"/>

	<jdbc:parameter name="IN_MAX_ROWS" expression="headers.maxRows"/>
	<jdbc:returning-resultset name="OUT_DATA" row-mapper="org.springframework.jdbc.core.ColumnMapRowMapper"/>
	<jdbc:returning-resultset name="OUT_DATA_2" row-mapper="org.springframework.jdbc.core.ColumnMapRowMapper"/>
</jdbc:stored-proc-outbound-gateway>

In the logs I see:

2013-02-05 10:52:14,326 DEBUG [org.springframework.jdbc.core.simple.SimpleJdbcCal l] (task-scheduler-2) JdbcCall call not compiled before execution - invoking compile
2013-02-05 10:52:14,409 DEBUG [org.springframework.jdbc.core.metadata.CallMetaDat aProviderFactory] (task-scheduler-2) Using org.springframework.jdbc.core.metadata.OracleCallM etaDataProvider
2013-02-05 10:52:14,410 DEBUG [org.springframework.jdbc.core.simple.SimpleJdbcCal l] (task-scheduler-2) Compiled stored procedure. Call string is [{call PKI_DATA.GET_DATA(?, ?, ?)}]
2013-02-05 10:52:14,413 DEBUG [org.springframework.jdbc.core.simple.SimpleJdbcCal l] (task-scheduler-2) SqlCall for procedure [PKI_DATA.GET_DATA] compiled
2013-02-05 10:52:14,416 DEBUG [org.springframework.jdbc.core.metadata.CallMetaDat aContext] (task-scheduler-2) Matching [] with [IN_MAX_ROWS]
2013-02-05 10:52:14,416 DEBUG [org.springframework.jdbc.core.metadata.CallMetaDat aContext] (task-scheduler-2) Found match for [IN_MAX_ROWS]
2013-02-05 10:52:14,419 DEBUG [org.springframework.jdbc.core.simple.SimpleJdbcCal l] (task-scheduler-2) The following parameters are used for call {call PKI_DATA.GET_DATA(?, ?, ?)} with: {IN_MAX_ROWS=200}
2013-02-05 10:52:14,419 DEBUG [org.springframework.jdbc.core.simple.SimpleJdbcCal l] (task-scheduler-2) 1: OUT_DATA SQL Type -10 Type Name null org.springframework.jdbc.core.SqlOutParameter
2013-02-05 10:52:14,419 DEBUG [org.springframework.jdbc.core.simple.SimpleJdbcCal l] (task-scheduler-2) 2: OUT_DATA_2 SQL Type -10 Type Name null org.springframework.jdbc.core.SqlOutParameter
2013-02-05 10:52:14,419 DEBUG [org.springframework.jdbc.core.simple.SimpleJdbcCal l] (task-scheduler-2) 3: IN_MAX_ROWS SQL Type 2 Type Name null org.springframework.jdbc.core.SqlParameter
2013-02-05 10:52:14,422 DEBUG [org.springframework.jdbc.core.JdbcTemplate] (task-scheduler-2) Calling stored procedure [{call PKI_DATA.GET_DATA(?, ?, ?)}]
2013-02-05 10:52:14,640 DEBUG [org.springframework.jdbc.core.JdbcTemplate] (task-scheduler-2) CallableStatement.execute() returned 'false'
2013-02-05 10:52:14,640 DEBUG [org.springframework.jdbc.core.JdbcTemplate] (task-scheduler-2) CallableStatement.getUpdateCount() returned -1
2013-02-05 10:52:14,644 INFO [com.my.spring.integration.logger] (task-scheduler-2) [Payload={OUT_DATA_2=[], OUT_DATA=[]}][Headers={timestamp=1360054334644, id=ab7cb28f-912a-4569-9b2c-d7eb6a1ab333}]

HTH

[dec_kelly]

Hi,

I seem to be seeing different behaviour? If I define 2 sql-parameter-definition (1 IN and 1 OUT) clauses and then 1 parameter and 1 returning-resultset, I would expect the proc to be called with 2 parameters, right?? Instead in the logs I see it trying to call the proc with 3 parameters.

I'm using SI 2.2.1.RELEASE and Spring Framework 3.1.3.RELEASE