Announcement Announcement Module
Collapse
No announcement yet.
Spring integration + oracle store procedures Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Spring integration + oracle store procedures

    Hi. Im trying to replicate the spring-integration store procedure coffee example from PostgreSQL to Oracle. Without success.
    Here is the link to the original code https://github.com/SpringSource/spri...res-postgresql

    I decided to start with just the procedure to get all the coffees, so I remove all the references to the getCoffee by name in the project.

    Here is the code to my store procedure:
    Code:
    create or replace 
    PROCEDURE find_all_coffee_beverages AS
    begin
        for x in (SELECT id, coffee_name, 
                    coffee_description
                    FROM coffee_beverages
                   ORDER BY ID) loop
            dbms_output.put_line
                (x.coffee_description);
        end loop;
    end;
    Here is my spring context:
    Code:
    <?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans"
    	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:int="http://www.springframework.org/schema/integration"
    	xmlns:int-jdbc="http://www.springframework.org/schema/integration/jdbc"
    	xsi:schemaLocation="http://www.springframework.org/schema/integration http://www.springframework.org/schema/integration/spring-integration.xsd
    		http://www.springframework.org/schema/integration/jdbc http://www.springframework.org/schema/integration/jdbc/spring-integration-jdbc.xsd
    		http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">
    
    	<bean id="dataSource" class="oracle.jdbc.pool.OracleDataSource"
    		destroy-method="close">
    		<property name="connectionCachingEnabled" value="true" />
    		<property name="URL" value="jdbc:oracle:thin:@//localhost:1521/XE" />
    		<property name="password" value="admin" />
    		<property name="user" value="admin" />
    		<property name="connectionCacheProperties">
    			<props merge="default">
    				<prop key="MinLimit">3</prop>
    				<prop key="MaxLimit">20</prop>
    			</props>
    		</property>
    	</bean>
    
    	<int:channel id="findAllProcedureRequestChannel" />
    
    	<int:gateway id="gateway" default-request-timeout="4000"
    		default-reply-timeout="4000"
    		service-interface="org.springframework.integration.service.CoffeeService">
    		<int:method name="findAllCoffeeBeverages" request-channel="findAllProcedureRequestChannel" />
    	</int:gateway>
    
    	<int-jdbc:stored-proc-outbound-gateway
    		id="outbound-gateway-storedproc-find-all" data-source="dataSource"
    		ignore-column-meta-data="true" request-channel="findAllProcedureRequestChannel"
    		expect-single-result="true" stored-procedure-name="FIND_ALL_COFFEE_BEVERAGES">
    		<int-jdbc:returning-resultset name="ref"
    			row-mapper="org.springframework.integration.support.CoffeBeverageMapper" />
    	</int-jdbc:stored-proc-outbound-gateway>
    
    	<bean id="transactionManager"
    		class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
    		<property name="dataSource" ref="dataSource" />
    	</bean>
    
    	<int:poller default="true" fixed-rate="5000">
    		<int:transactional />
    	</int:poller>
    
    </beans>
    So I compile it and run it with maven like this: mvn clean package exec:java -e
    It compiles perfectly but when it try to run I get the next exceptions:

    Code:
    [INFO] --- exec-maven-plugin:1.2:java (default-cli) @ oracle-stored-procedures ---
    org.springframework.jdbc.BadSqlGrammarException: CallableStatementCallback; bad SQL grammar [{call FIND_ALL_COFFEE_BEVERAGES(?)}]; nested exception is java.sql.SQLException: ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to 'FIND_ALL_COFFEE_BEVERAGES'
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored
    
    	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:233)
    	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1030)
    	at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1064)
    	at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal(AbstractJdbcCall.java:388)14:01:56.804 WARN  [org.springframework.integration.Main.main()][org.springframework.integration.gateway.GatewayProxyFactoryBean$MethodInvocationGateway] failure occurred in gateway sendAndReceive
    org.springframework.integration.MessageHandlingException: error occurred in message handler [org.springframework.integration.jdbc.StoredProcOutboundGateway#0]
    	at org.springframework.integration.handler.AbstractMessageHandler.handleMessage(AbstractMessageHandler.java:79)
    	at org.springframework.integration.dispatcher.UnicastingDispatcher.doDispatch(UnicastingDispatcher.java:115)
    	at org.springframework.integration.dispatcher.UnicastingDispatcher.dispatch(UnicastingDispatcher.java:102)
    	at org.springframework.integration.channel.AbstractSubscribableChannel.doSend(AbstractSubscribableChannel.java:77)
    	at org.springframework.integration.channel.AbstractMessageChannel.send(AbstractMessageChannel.java:157)
    	at org.springframework.integration.core.MessagingTemplate.doSend(MessagingTemplate.java:288)
    	at org.springframework.integration.core.MessagingTemplate.doSendAndReceive(MessagingTemplate.java:318)
    	at org.springframework.integration.core.MessagingTemplate.sendAndReceive(MessagingTemplate.java:239)
    	at org.springframework.integration.core.MessagingTemplate.convertSendAndReceive(MessagingTemplate.java:274)
    	at org.springframework.integration.gateway.MessagingGatewaySupport.doSendAndReceive(MessagingGatewaySupport.java:224)
    	at org.springframework.integration.gateway.MessagingGatewaySupport.sendAndReceive(MessagingGatewaySupport.java:203)
    	at org.springframework.integration.gateway.GatewayProxyFactoryBean.invokeGatewayMethod(GatewayProxyFactoryBean.java:306)
    	at org.springframework.integration.gateway.GatewayProxyFactoryBean.doInvoke(GatewayProxyFactoryBean.java:269)
    	at org.springframework.integration.gateway.GatewayProxyFactoryBean.invoke(GatewayProxyFactoryBean.java:260)
    	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
    	at $Proxy16.findAllCoffeeBeverages(Unknown Source)
    	at org.springframework.integration.Main.main(Main.java:67)
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    	at java.lang.reflect.Method.invoke(Method.java:597)
    	at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:291)
    	at java.lang.Thread.run(Thread.java:662)
    Does anyone knows why am I getting that exceptions?
    Thanks in advance.

  • #2
    Gunnar is looking into it (based on your StackExchange post). For some reason, this post needed approval - the forum software marked it so.

    I deleted the duplicate post.
    Last edited by Gary Russell; Feb 27th, 2013, 04:21 PM.

    Comment


    • #3
      Hi!

      You have bad procedure declaration. You are expecting some ResultSet, but you don't provide one in the procedure.
      It must be like this:
      Code:
      create or replace FUNCTION find_all_coffee_beverages 
         RETURN SYS_REFCURSOR
      AS
        my_cursor SYS_REFCURSOR;
      begin
        OPEN my_cursor FOR 
              SELECT id, coffee_name, 
                      coffee_description
                      FROM coffee_beverages
                     ORDER BY ID;
        RETURN my_cursor;
      end;
      Then, of course, you have to add is-function="true" to the <int-jdbc:stored-proc-outbound-gateway>

      Take care,
      Artem

      Comment


      • #4
        Worked perfectly thanks.

        Now for the function that looks for coffees:
        Code:
        create or replace 
        FUNCTION find_coffee_beverages (pid in integer)
           RETURN varchar2
        AS
          description varchar2(25);
        begin
            SELECT coffee_description
            into description
            FROM coffee_beverages
            WHERE ID = pid
            ORDER BY ID;
          RETURN description;
        end;
        and in the context:

        Code:
        	<int-jdbc:stored-proc-outbound-gateway
        		id="outbound-gateway-storedproc-find-coffee" data-source="dataSource"
        		request-channel="findCoffeeProcedureRequestChannel"
        		skip-undeclared-results="true" stored-procedure-name="FIND_COFFEE_BEVERAGES"
        		expect-single-result="true" is-function="true">
        		<int-jdbc:parameter name="PID" expression="payload" />
        	</int-jdbc:stored-proc-outbound-gateway>
        Hope this helps to anyone with a similar doubt.
        Last edited by linker85; Feb 27th, 2013, 05:37 PM. Reason: 1 more question

        Comment


        • #5
          Hi,

          I discovered a bug in the Stored Procedure Inbound Channel Adapter. The "is-function" attribute was not set correctly. Please follow Jira INT-2945:

          https://jira.springsource.org/browse/INT-2945.

          I already fixed the issue and submitted a pull request. Also, please see INTSAMPLES-107:

          https://jira.springsource.org/browse/INTSAMPLES-107.

          I have been converting the Postgres Stored Procedure/Function sample you reference above to Oracle and added it to the Oracle Sample. Still needs some clean-up but will push the code later tonight.

          Cheers,

          Comment


          • #6
            Hi,

            It took a little longer but I have expanded the Oracle Stored Procedure Example (Converting/adding the Postgres Example) with improved documentation:

            https://github.com/SpringSource/spri...cedures-oracle

            Cheers,

            Gunnar

            Comment


            • #7
              Hi I am trying to use this same for calling stored proc,

              <code>
              <int-jdbc:stored-proc-outbound-gateway
              id="cbor-stored-proc"
              data-source="oracleDataSource"
              request-channel="getLedgerTransactionsInputChannel"
              reply-channel="getLedgerTransactionsOutputChannel"
              skip-undeclared-results="true"
              is-function="false"
              stored-procedure-name="ENROLLMENT_P.PKG_KRE_ENROLL.P_PRE_ENROLL_GROUP_MAINT"
              expect-single-result="true">
              <int-jdbc:sql-parameter-definition name="IV_GRP_ID" direction="IN" type="VARCHAR" scale="32767"/>
              <int-jdbcarameter name="IV_GRP_ID" expression="payload" />

              </int-jdbc:stored-proc-outbound-gateway>
              </code>

              What I have here is, schema-package-storedprocedure name,could you please let me know if I miss something here, I am getting exception. Kindly help

              Caused by: org.springframework.dao.InvalidDataAccessApiUsageE xception: Unable to determine the correct call signature for PKG_KRE_ENROLL.P_PRE_ENROLL_GROUP_MAINT - package name should be specified separately using '.withCatalogName("PKG_KRE_ENROLL")'
              at org.springframework.jdbc.core.metadata.GenericCall MetaDataProvider.processProcedureColumns(GenericCa llMetaDataProvider.java:308)
              at org.springframework.jdbc.core.metadata.GenericCall MetaDataProvider.initializeWithProcedureColumnMeta Data(GenericCallMetaDataProvider.java:102)

              Comment


              • #8
                For Oracle procedures you should use this one:
                HTML Code:
                ignore-column-meta-data="true"
                Your procedured is in the package and driver can't determine it's metadata in this case.

                Cheers

                Comment

                Working...
                X