This article outlines how to set a dynamic endpoint in an OSB HTTP Business Service. The endpoint is retrieved from a routing table which resides in an Oracle 12c database.
Components used for this solution:
- Ubuntu Linux 14.04 64bit
- JDeveloper, running the Quick Start Oracle Fusion Middleware suite
- Oracle Service Bus 12c
- Oracle Weblogic 12c
- OSB Project location:
- Oracle Virtualbox Developer Days image for DB 12c, running:
- Oracle Database 12c
- Oracle SQL Developer 4
- JDeveloper, running the Quick Start Oracle Fusion Middleware suite
This picture shows the running solution in the OSB test console:
Database table preparation
We need a routing table in our schema, I’m using this table setup:
CREATE TABLE "C##JORIS"."ROUTINGTABLE" ( "ROUTE" VARCHAR2(50), "ENDPOINT" VARCHAR2(100) );
Insert into ROUTINGTABLE (ROUTE,ENDPOINT) values ('SalesOrder','http://localhost:7101/salesEndpoint'); Insert into ROUTINGTABLE (ROUTE,ENDPOINT) values ('FinanceReceipt','http://localhost:7101/financeEndpoint');
Weblogic configuration: JDBC Data Source
http://127.0.0.1:7101/console/
Navigation in Console: DefaultDomain - Services - Data Sources
JDeveloper: Oracle Service Bus project
1.) Assign $route: node-name($body/*[1]) This assign determines our routing key. It is the same key as the first column in the routing table. The XPath here is used to select the name of the first node but you can change this to what you want to route on.
2.) Assign $query: fn:concat("select ENDPOINT from ROUTINGTABLE where ROUTE = '", $route, "'") This assign determines the query which will be executed in the next step. We want to select the ENDPOINT which belongs to the ROUTE which was assigned in step 1.
3.) Assign $query: (fn-bea:execute-sql( xs:string("LocalDB"), xs:string("ENDPOINT"), $query )/*:ENDPOINT)[1] This assign actually executes the SQL query to our database, which is the first argument. The second argument names the re-occurring rows, in this case "ENDPOINT". The thirst argument is the query to execute. The XPath after the execute-sql statement is to make sure we only get one endpoint.
$endpoint/text()