1 Reply Latest reply: Sep 19, 2011 12:37 PM by Mikereiche-Oracle RSS

    Change datasrouce from a physical service in Runtime

      I need to implement a service that expose the same data from different database instances. I have one datasource per office and at the service call the webservice's user will inform me from what office he/she wants the data to come from.

      Does anyone know how could I change the datasource from a physical service during runtime based on this parameter?

      Thanks a lot.

      Victor Greggio
        • 1. Re: Change datasrouce from a physical service in Runtime
          I can think of two different ways.

          1) straight-forward/brute-force : create separate folders in your dataspace for each database. The contents of each folder will be indentical except for the (a) the folder; and (2) the physical dataservice for the database. And then you access the dataservices within the folder corresponding to the database. The advantage is that there is nothing special, it's obvious how it works, and it will definitely work. The disadvantage is that you'll need to maintain a copy for each database.

          2) You could implement a SourceBindingProvider to return a different datasource at runtime. The problem is there is no argument, therefore no way to choose datasources based on data (it was intended to give different datasources for different users). To get around this, you could use a ThreadLocal object that you could set in a separate call (which would be read later by the SourceBindingProvider). The advantage is that you only need one copy of your dataservices, and more DataSources can be added by simply adding them to dataSourceNames array. The disadvantages are (1) it's complicated; (2) you will need to understand advanced ODSI concepts (java functions, SourceBindingProvider, how to trick ODSI into calling a function that does not return anything without affecting the result); and (3) I believe you need a patch to make part of this (searching all the datasources in one query) work. And the last disadvantage is that you are pretty much on your own. Customer Support will not be able to help you with this, and I might not have time to. Take special note that ODSI will not call functions just because they appear in your query (specifically the setRouteNumber() function. ODSI nees to believe that function is require to produce the result.

          Here's your SourceBindingProvider implementation plus the Java Functions for setting and getting the "Route Number" to determine which datasource to use.

          public class RoutingSourceBindingProvider implements com.bea.ld.bindings.SourceBindingProvider{
               private static String[] dataSourceNames={
               public String getBinding(String dataSourceName, boolean isUpdate) {
                    Integer routeNumber=getRouteNumber();
                    if(routeNumber == null){
                         System.err.println("RoutingSourceBindingProvider: RouteNumber is null");
                    } else {
          dataSourceName=dataSourceNames[routeNumber.intValue() % dataSourceNames.length];
                    System.out.println("The data source is " + dataSourceName);
                    return dataSourceName;

          private static ThreadLocal routeNumber = null;

          public static XmlObject setRouteNumber( Integer routeNum){
               routeNumber=new ThreadLocal();
               return null;

          public static Integer[] getRouteNumbers( ){
          Integer[] numbers=new Integer[dataSourceNames.length];
          for(int i=0; i< numbers.length; i++)
          numbers=new Integer(i);
          return numbers;

          private static Integer getRouteNumber( ){
          return null;
               return (Integer)routeNumber.get();


          Here is the Physical Data Source created from the above "Java Function". Do not copy it, re-create it in ODSI Studio.

          xquery version "1.0" encoding "WINDOWS-1252";

          (::pragma xfl <x:xfl xmlns:x="urn:annotations.ld.bea.com">
          <javaFunction class="RoutingSourceBindingProvider"/></x:xfl> ::)

          declare namespace tns="lib:CALDB_phy_gen/SetRoute";

          (::pragma function <f:function xmlns:f="urn:annotations.ld.bea.com" nativeName="setRouteNumber">

          <param nativeType="java.lang.Integer"/>

          declare function tns:setRouteNumber($x1 as xsd:int) as element()* external;

          (::pragma function <f:function xmlns:f="urn:annotations.ld.bea.com" nativeName="getRouteNumbers">
          <nonCacheable/> </f:function>::)

          declare function tns:getRouteNumbers() as xsd:int* external;


          And here is a logical dataservice that first sets the "Route Number", so that the SourceBindingProvider returns a different datasource.

          xquery version "1.0" encoding "WINDOWS-1252";
          (::pragma xds <x:xds targetType="tgt:HEALTH_10M_STATSUSAGE_DTL_00" xmlns:tgt="http://www.mycompany.com/RDS/CALDB" xmlns:x="urn:annotations.ld.bea.com">
          <userDefinedView/></x:xds> ::)

          declare namespace ds="ld:CALDB_phy_gen/HEALTH_10M_STATSUSAGE_DTL_AGG";

          (: not sure if all these namespace definitions are required, or if they just happened to be left behind in my sample :)

          declare namespace src0="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_00";
          declare namespace src1="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_01";
          declare namespace src2="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_02";
          declare namespace src3="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_03";
          declare namespace src4="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_04";
          declare namespace src5="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_05";
          declare namespace src6="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_06";
          declare namespace src7="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_07";
          declare namespace src8="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_08";
          declare namespace src9="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_09";
          declare namespace src10="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_10";
          declare namespace src11="ld:CALDB_phy_gen/internal/HEALTH_10M_STATSUSAGE_DTL_AGG_11";
          import schema namespace tgt="http://www.mycompany.com/RDS/CALDB" at "ld:CALDB_phy_gen_schema/schemas/HEALTH_10M_STATSUSAGE_DTL_00.xsd";
          declare namespace route="lib:CALDB_lib/RouteLib";
          declare namespace ns1="lib:CALDB_phy_gen/SetRoute";

          (::pragma function <f:function kind="read" xmlns:f="urn:annotations.ld.bea.com">
          </f:function> ::)
          (: this function will search through all the datasources,
          ***** Victor, this was not in your requirement, and you'll need a patch to prevent the SourceBinding from being cached :)
          declare function ds:getHEALTH_10M_STATSUSAGE_AGG($poolID as xs:integer, $startCubeID as xs:integer, $endCubeID as xs:integer) as element(tgt:HEALTH_10M_STATSUSAGE_DTL_00)*
          for $route in ns1:getRouteNumbers() (: here's the list of RouteNumbers :)
          where empty ( ns1:setRouteNumber($route) ) (: here's how we get ODSI to call the setRouteNumber($route) function :)
          src0:getHEALTH_10M_STATSUSAGE_AGG($poolID, $startCubeID, $endCubeID)
          (::pragma function <f:function kind="read" xmlns:f="urn:annotations.ld.bea.com">
          </f:function> ::)
          declare function ds:getHEALTH_10M_STATSUSAGE_AGG_route($route_key as xs:integer, $poolID as xs:integer, $startCubeID as xs:integer, $endCubeID as xs:integer) as element(tgt:HEALTH_10M_STATSUSAGE_DTL_00)*
          for $x in 1
          where empty ( ns1:setRouteNumber($route_key) ) (: here's how we get ODSI to call setRouteNumber() :)
          for $var in src0:getHEALTH_10M_STATSUSAGE_AGG($poolID, $startCubeID, $endCubeID)
          return $var