Spring integration + oracle store procedures

Hi. I´m 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.

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.

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

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.

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,

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