252 lines
8.2 KiB
Markdown
252 lines
8.2 KiB
Markdown
Spring Integration - Stored Procedure Example - Oracle
|
|
======================================================
|
|
|
|
## Overview
|
|
|
|
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][]
|
|
|
|
## Running the Sample
|
|
|
|
### Pre-requisites
|
|
|
|
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)
|
|
|
|
### JDBC Driver Installation for Oracle
|
|
|
|
- Go to [https://www.oracle.com/technetwork/indexes/downloads/index.html](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](https://www.gradle.org/docs/current/userguide/application_plugin.html):
|
|
|
|
$ gradlew :stored-procedures-oracle:run
|
|
|
|
### Common Oracle Setup
|
|
|
|
#### Create Tablespace
|
|
|
|
```SQL
|
|
CREATE TABLESPACE procedure_test
|
|
DATAFILE 'c:/data/procedure_test.dbf'
|
|
SIZE 10M
|
|
AUTOEXTEND ON NEXT 10M
|
|
MAXSIZE 100M;
|
|
```
|
|
|
|
#### Create User
|
|
|
|
```SQL
|
|
CREATE USER storedproc
|
|
IDENTIFIED BY storedproc
|
|
DEFAULT TABLESPACE procedure_test
|
|
TEMPORARY TABLESPACE temp;
|
|
```
|
|
|
|
#### Grant Rights
|
|
|
|
```SQL
|
|
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO storedproc;
|
|
ALTER USER storedproc QUOTA unlimited ON procedure_test;
|
|
```
|
|
|
|
### Setting up the Spring Application Context
|
|
|
|
You may have to update the Oracle DB properties in:
|
|
|
|
/src/main/resources/META-INF/spring/integration/spring-integration-context.xml
|
|
|
|
```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>
|
|
```
|
|
|
|
## Running Sample 1 - Capitalizes Strings
|
|
|
|
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.
|
|
|
|
### Creating the Stored Procedure
|
|
|
|
```SQL
|
|
create or replace
|
|
PROCEDURE CAPITALIZE_STRING(inoutString IN OUT VARCHAR) IS
|
|
BEGIN
|
|
SELECT upper(inoutString) INTO inoutString from dual ;
|
|
END;
|
|
```
|
|
|
|
### Creating the Stored Function
|
|
|
|
```SQL
|
|
create or replace
|
|
FUNCTION GET_COOL_NUMBER
|
|
RETURN NUMBER
|
|
IS cool_number NUMBER(11,2);
|
|
BEGIN
|
|
cool_number := 12345;
|
|
RETURN cool_number;
|
|
END;
|
|
```
|
|
|
|
### Execute the Sample
|
|
|
|
* 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`.
|
|
|
|
## Running Sample 2 - Coffee Service
|
|
|
|
### Create Table COFFEE_BEVERAGES
|
|
|
|
```SQL
|
|
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"));
|
|
```
|
|
### Add Sample Data to Table COFFEE_BEVERAGES
|
|
|
|
```SQL
|
|
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.');
|
|
```
|
|
|
|
### Creating the Stored Functions
|
|
|
|
Please create the following Stored Functions:
|
|
|
|
#### Find All Coffee Beverages
|
|
|
|
```SQL
|
|
create or replace
|
|
package types
|
|
as
|
|
type cursorType is ref cursor;
|
|
end;
|
|
```
|
|
|
|
```SQL
|
|
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;
|
|
```
|
|
#### Find Specific Coffee Beverage
|
|
|
|
```SQL
|
|
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;
|
|
```
|
|
### Execute the Sample
|
|
|
|
* 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
|
|
|
|
[Stored Procedure Sample for PostgreSql]: https://github.com/ghillert/spring-integration-samples/tree/main/intermediate/stored-procedures-postgresql
|