Database Connector | How to execute a "MERGE" SQL statement in an Oracle Database using the Bulk Update operation

DESCRIPTION

This article will show an example of how to execute a "MERGE" SQL statement using the Bulk Update operation of the Database Connector.

In the example, we'll use a "SELECT" statement from the table named "SourceColors" having the attribute ColorName valued as "Cyan" as the source for the "MERGE" statement, and then we'll verify if the "ColorID" matches one in the "TargetColors" table. If there is no match in "ColorID", then the query will insert a record with that "ColorID" and "ColorName" into the target table.

You'll find attached the project "db-oracle-merge.jar" implementing this use case.

STEPS TO FOLLOW

1. Define the DB objects necessary for the operation to run.

In the case of this example:

--Table Creation--


CREATE TABLE SourceColors(
ColorID INT,
ColorName VARCHAR(50)
);

CREATE TABLE TargetColors(
ColorID INT,
ColorName VARCHAR(50)
);


--Data insertion--


INSERT INTO SourceColors(ColorID,ColorName) VALUES(1,'Red');
INSERT INTO SourceColors(ColorID,ColorName) VALUES(2,'Blue');
INSERT INTO SourceColors(ColorID,ColorName) VALUES(3,'Orange');
INSERT INTO SourceColors(ColorID,ColorName) VALUES(4,'Cyan');
INSERT INTO SourceColors(ColorID,ColorName) VALUES(5,'Cyan');
INSERT INTO SourceColors(ColorID,ColorName) VALUES(6,'Cyan');
INSERT INTO SourceColors(ColorID,ColorName) VALUES(7,'Cyan');
INSERT INTO SourceColors(ColorID,ColorName) VALUES(8,'Cyan');


INSERT INTO TargetColors(ColorID,ColorName) VALUES(1,'Red');
INSERT INTO TargetColors(ColorID,ColorName) VALUES(2,'Blue');
INSERT INTO TargetColors(ColorID,ColorName) VALUES(5,'Green');
INSERT INTO TargetColors(ColorID,ColorName) VALUES(6,'Violet');

2. In Anypoint Studio, create a Mule Application with a Source/Inbound endpoint to trigger the flow.

EG.

<http:listener-config name="HTTP_Listener_config" doc:name="HTTP Listener config">
		<http:listener-connection host="0.0.0.0" port="8081" />
</http:listener-config>

<flow name="db-oracle-mergeFlow" >
		<http:listener doc:name="Listener" doc:id="377e235f-3467-414a-b320-69d6d1d46ae5" config-ref="HTTP_Listener_config" path="/merge"/>
</flow>

3. Drag a Bulk Update operation from the Database connector into the canvas, set up your Database credentials, add the required driver, and finally write the MERGE statement into the "SQL Query Text" section.

Your Mule Application's XML file will look similar to the one below:

<?xml version="1.0" encoding="UTF-8"?>

<mule xmlns:http="http://www.mulesoft.org/schema/mule/http" xmlns:db="http://www.mulesoft.org/schema/mule/db"
xmlns="http://www.mulesoft.org/schema/mule/core"
xmlns:doc="http://www.mulesoft.org/schema/mule/documentation" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.mulesoft.org/schema/mule/core http://www.mulesoft.org/schema/mule/core/current/mule.xsd
http://www.mulesoft.org/schema/mule/db http://www.mulesoft.org/schema/mule/db/current/mule-db.xsd
http://www.mulesoft.org/schema/mule/http http://www.mulesoft.org/schema/mule/http/current/mule-http.xsd">

<http:listener-config name="HTTP_Listener_config" doc:name="HTTP Listener config">
<http:listener-connection host="0.0.0.0" port="8081" />
</http:listener-config>

<db:config name="Database_Config" doc:name="Database Config">
<db:oracle-connection host="yourHost" port="yourPort" user="yourUser" password="yourPassword" instance="yourInstance" />
</db:config>

<flow name="db-oracle-mergeFlow">

<http:listener doc:name="Listener" config-ref="HTTP_Listener_config" path="/merge"/>

<db:bulk-update config-ref="Database_Config">
<db:bulk-input-parameters ><![CDATA[#[[{'colorname':'Cyan'}]]]]></db:bulk-input-parameters>
<db:sql ><![CDATA[MERGE INTO TargetColors Target 
USING (SELECT * FROM SourceColors WHERE ColorName = :colorname) Source ON (Source.ColorID = Target.ColorID) 
WHEN NOT MATCHED THEN 
INSERT (ColorID,ColorName) VALUES (Source.ColorID,Source.ColorName)]]></db:sql>
</db:bulk-update>

<logger level="INFO" doc:name="Logger" message="Done!"/>

</flow>

</mule>

4. Run the application and trigger the endpoint. 

In this example:

http://0.0.0.0:8081/merge

5. Verifying the results by executing a "SELECT" statement into the table "TargetColors" should show a result similar to the one below.

SELECT * From TargetColors

1,Red
2,Blue
5,Green
6,Violet
4,Cyan
7,Cyan
8,Cyan

Disclaimer: This solution provides a suggestion that should be considered in conjunction with your specific use-case and requirements and does not represent a complete solution for all circumstances.