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

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

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.

Code:

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

Code:

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

HTML Code:

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.

Code:

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

Code:

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.

Code:

StoredProcOutboundGateway.handleRequestMessage(Message<?> requestMessage)

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

Thanks

Hi!

Your config should be like this:

HTML Code:

<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

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)

Code:

<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"/>  <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

Code:

<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???

Try this:

Code:

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 );

HTML Code:

<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:

Quote:

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

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