This sample application illustrates the usage of the Spring Integration Stored Procedure components using an Oracle™ database as a backend. Actually 2 samples are provided:

• Sample 1 Capitalizes Strings
• Sample 2 Retrieves Coffee Data
  • This is sample is similar to the Stored Procedure Sample for PostgreSql

This sample was tested against: Oracle Database Express Edition 11g Release 2 (which can be downloaded and used for free). Nevertheless, the example should work with other versions as well.

• Access to a Oracle or Oracle XE database instance
• Install Oracle JDBC Driver to your local Maven repository (~/.m2)

• Go to https://www.oracle.com/technetwork/indexes/downloads/index.html

• Under "JDBC Drivers", download the appropriate driver relevant to your Oracle and JDK version (This sample was tested using "Oracle Database 11g Release 2 JDBC Drivers")

• Once downloaded, install the driver to your local Maven repository:

    $ mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -Dversion=11.2.0.3 -Dpackaging=jar -Dfile=~/dev/ojdbc6.jar -DgeneratePom=true
  

• Now you can uncomment the ojdbc6 dependency in the build.gradle file for stored-procedures-oracle project.

After that you can run the sample application using Gradle Application Plugin:

$ gradlew :stored-procedures-oracle:run

	CREATE TABLESPACE procedure_test
	DATAFILE 'c:/data/procedure_test.dbf'
	   SIZE 10M
	   AUTOEXTEND ON NEXT 10M
	   MAXSIZE 100M;

	CREATE USER storedproc
	       IDENTIFIED BY storedproc
	       DEFAULT TABLESPACE procedure_test
	       TEMPORARY TABLESPACE temp;

	GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO storedproc;
	ALTER USER storedproc QUOTA unlimited ON procedure_test;

You may have to update the Oracle DB properties in:

/src/main/resources/META-INF/spring/integration/spring-integration-context.xml

    <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="storedproc" />
	   <property name="user"     value="storedproc" />
	   <property name="connectionCacheProperties">
	      <props merge="default">
	         <prop key="MinLimit">3</prop>
	         <prop key="MaxLimit">20</prop>
	      </props>
	   </property>
	</bean>

This example provides a simple example using the stored procedure outbound gateway adapter. This example will call an Oracle Stored Procedure as well as an Oracle Function using the StoredProc Outbound Gateway.

create or replace
PROCEDURE CAPITALIZE_STRING(inoutString IN OUT VARCHAR) IS
BEGIN
    SELECT upper(inoutString) INTO inoutString from dual ;
END;

create or replace
FUNCTION GET_COOL_NUMBER
   RETURN NUMBER
   IS cool_number NUMBER(11,2);
BEGIN
   cool_number := 12345;
   RETURN cool_number;
END;

• running the "Main" class from within STS (Right-click on Main class --> Run As --> Java Application)

• or from the command line:

  $ gradlew :stored-procedures-oracle:run

You should see the following output:

16:05:19.556 INFO  [main][org.springframework.integration.samples.storedprocedure.Main]
=========================================================

          Welcome to Spring Integration's
     Stored Procedure/Function Sample for Oracle

    For more information please visit:
    https://www.springsource.org/spring-integration

=========================================================
Please enter a choice and press <enter>:
	1. Execute Sample 1 (Capitalize String)
	2. Execute Sample 2 (Coffee Service)
	q. Quit the application

Select Option 1.

=========================================================

    Please press 'q + Enter' to quit the application.

=========================================================
Please enter a string and press <enter>: hello world
Converting String to Uppcase using Stored Procedure...
Retrieving Numeric value via Sql Function...
Converted 'hello world' - End Result: 'HELLO WORLD_12345'.

When you enter a text, the text will be converted into upper-case using the Oracle Stored Procedure named CAPITALIZE_STRING. Afterwards, the String is concatenated with the result from calling the Oracle Stored Function GET_COOL_NUMBER.

CREATE TABLE "COFFEE_BEVERAGES"(
  "ID"                 NUMBER(10,0)       NOT NULL,
  "COFFEE_NAME"        VARCHAR2(50 CHAR)  NOT NULL,
  "COFFEE_DESCRIPTION" VARCHAR2(500 CHAR) NOT NULL,
  CONSTRAINT "COFFEE_BEVERAGES_PK" PRIMARY KEY ("ID"));

REM INSERTING into COFFEE_BEVERAGES
SET DEFINE OFF;
Insert into COFFEE_BEVERAGES (ID,COFFEE_NAME,COFFEE_DESCRIPTION) values (1,'Espresso','Espressos keep developers going in the morning. There are never enough of them.');
Insert into COFFEE_BEVERAGES (ID,COFFEE_NAME,COFFEE_DESCRIPTION) values (2,'Cappuccino','For the finer moments. Wrap your espresso in a tasty layer of foam.');
Insert into COFFEE_BEVERAGES (ID,COFFEE_NAME,COFFEE_DESCRIPTION) values (3,'Mocha','Mmmmh, chocolate.');
Insert into COFFEE_BEVERAGES (ID,COFFEE_NAME,COFFEE_DESCRIPTION) values (4,'Latte','If you are more into milk than into foam.');

Please create the following Stored Functions:

create or replace
package types
as
    type cursorType is ref cursor;
end;

create or replace
FUNCTION find_all_coffee_beverages return types.cursortype
AS
  l_cursor types.cursorType;
BEGIN
  OPEN l_cursor FOR SELECT "ID", "COFFEE_NAME", "COFFEE_DESCRIPTION" FROM "COFFEE_BEVERAGES";
  RETURN l_cursor;
END;

create or replace
FUNCTION find_coffee(coffee_id IN integer)
  RETURN VARCHAR2 is description VARCHAR2(1000);
begin
    SELECT COFFEE_DESCRIPTION into description from COFFEE_BEVERAGES where ID=coffee_id;
    return description;
end;

• running the "Main" class from within STS (Right-click on Main class --> Run As --> Java Application)

• or from the command line:

  $ gradlew :stored-procedures-oracle:run

You should see the following output:

16:05:19.556 INFO  [main][org.springframework.integration.samples.storedprocedure.Main]
=========================================================

          Welcome to Spring Integration's
     Stored Procedure/Function Sample for Oracle

    For more information please visit:
    https://www.springsource.org/spring-integration

=========================================================
Please enter a choice and press <enter>:
	1. Execute Sample 1 (Capitalize String)
	2. Execute Sample 2 (Coffee Service)
	q. Quit the application

Select Option 2.

This should result in the following output:

* Please enter 'list' and press <enter> to get a list of coffees.
* Enter a coffee id, e.g. '1' and press <enter> to get a description.
* Please press 'q + Enter' to quit the application.

This sample also periodically polls the Oracle database using a Stored Procedure Inbound Channel Adapter:

16:06:46.669 INFO  [task-scheduler-1][org.springframework.integration.handler.LoggingHandler] [Payload=[CoffeeBeverage [id=1,...

For help please take a look at the Spring Integration documentation:

https://www.springsource.org/spring-integration