Skip to end of metadata
Go to start of metadata

Here is small tip on how to utilize oracle functions using the JDBC Receiver Adapter .

P.S If its a new functionality that you are trying to achieve its always better to create a stored procedure on the DB end , but what if you would like to utilize an already existing function .

I don't know if everybody knows this but i recently found out that you can invoke a user defined function in oracle using a  Select query  and the function would return you whatever its programmed to return . Once you know this its pretty simple .

Your inbound Message type for the receiver JDBC adapter which follows the structure for SQL_QUERY

     <?xml version="1.0" encoding="UTF-8"?>
<ns0:MT_IB xmlns:ns0="http://yournamespace">
    <STATEMENT>
        <FUNCTION ACTION="SQL_QUERY">
            <ACCESS>select db.funciton_name($input$) "outResponse" from dual</ACCESS>
            <KEY>
                <input>"xyz"</input>
            </KEY>
        </FUNCTION>
    </STATEMENT>
</ns0:MT_IB>

the statement in the node <ACCESS> will invoke the specified function . Keep in mind that this scenario explained here is a synchronous call as a user-defined function will always return something .So you can specify the return variable name in the statement itself which here is 'outputResponse ' .

The  response Message Type would look like

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

<ns0:MT_IB_response xmlns:ns0="http://yournamespace">
   <STATEMENT_response>
      <outResponse>
         <isValid/>
      </outResponse>
   </STATEMENT_response>
</ns0:MT_IB_response>

  • No labels