OSB - fn-bea:execute-sql in XQuery Transformation

Problem: In an XQuery transformation file, an SQL query needs to be run to transform a date into the acceptable ISO date format to insert it into the database. Unlike the query-database function used in the SOA engine for XSLTs, the fn-bea:execute-sql returns the result enclosed in two XML elements. For example:

fn-bea:execute-sql('jdbc/DataSource',xs:QName('Element'),'select 123 result from DUAL')

Returns the value as such:

<Element>
    <RESULT>123</RESULT>
</Element>

This isn't really helpful when you want to just get the result itself (without surrounded elements) to use it in an XQuery transformation.

Solution:
I had to create a local function that would only return the data (in the above example, return 123). The $input is the SQL query that needs to be run.

declare function local:stringToDate($input as xs:string){
    let $value:=fn-bea:execute-sql('jdbc/DataSource',xs:QName('Element'),$input)
    return fn:data($value/RESULT)
};

In this case, the SQL query I needed to run to transform the string date (e.g. 03/16/17 9:17 PM) into the ISO date format was:

select to_char(to_date('03/16/17 9:17 PM', 'MM/DD/YY HH:MI AM'),'YYYY-MM-DD"T"HH24:MI:SS') from dual;

And then I had call it as such:

<ns2:date>{local:stringToDate(fn:concat('select to_char(to_date(''',$inDate,''',''MM/DD/YY HH:MI AM''),''YYYY-MM-DD&quot;T&quot;HH24:MI:SS'') result from dual'))}</ns2:date>

Mediator - Instances stay in Running

This is a known bug where Mediator instances stay in running mode. Check the following Oracle Support Document for more information:

Oracle Support Document

To switch from 'Deferred' to 'Immediate' auditing:

1.  Go to EM

2.  Expand 'SOA' on the left

3.  Right click 'soa-infra'

4.  Select 'SOA Administration' -> 'Common Properties'

5.  At the bottom select 'More SOA Infra Advanced Configuration Properties...'

6.  Select 'Audit Config'

7.  Expand 'policies'

8.  Here you'll see 'Element_0' and 'Element_1'

9.  Expand 'Element_1' and change 'isActive' to 'false'

10.  Expand 'Element_0' and change 'isActive' to 'true'

11.  Click 'Apply'