Announcement Announcement Module
Collapse
No announcement yet.
stored-proc-outbound-gateway & Oracle Stored Procedure with OUT SYS_REF_CURSOR Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      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"/>
      	                 <!-- -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
      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???

      Comment


      • #4
        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:
        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

        Comment


        • #5
          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

          Comment

          Working...
          X