Spring integration + oracle store procedures

[linker85]

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:

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:

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

[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.

[Gary Russell]

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.

[Cleric]

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:

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

[linker85]

Worked perfectly thanks.

Now for the function that looks for coffees:

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:

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

[ghillert]

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,

[ghillert]

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