Problem : I want to check email notification history in SOA Suite 11g FMW Console. When was an email sent, to whom and from whom and blaw blaw blaw.
Solution : Just go to FMW Console > Farm_Domain > User Messaging Service > Right Click usermessagingserver(soa_server1) > Message Status. It shows you the email notifications details. You can use the search criteria also to filter the data.
oraext:query-database() is a wonderful function that can be used to
- Fetch the data in the Assigns/Transformations on the fly without the use of DB Adapter
- Can be used to run any Dynamic Custom SQL including variable IN parameters and GROUP BY clauses.
However, the output returned from this function is always a long string depending on the no.of resultant records. But we can always transform the same into XML format to traverse it in an easier way across the process.
1. While calling the function, use the 2nd and 3rd parameters as true() rather than false(). It will ensure that ROWSET and ROW tags get generated in the resultant String.
2. Now create a schema with ROWSET as root element and ROW as unbounded repeating element. The best way to do this is to copy the resultant XML string into an XML file and use Schema from XML file option to create the schema. This will create the schema for your resultant output string. Now you can create a variable of this schema to store the returned string in the XML format.
Note: The returned string from query-database() function does not contain any namespace, whereas the schema created above would contain a namespace. Hence remove the following attributes explicitly from your schema so created. It is also advisable to validate/create your schema using tools like XMLSpy etc. for better confidence.
3. Now in the Assign activity use this function as follows to assign the result to Schema element just created in above step. You can use any different query or the variable that contains your query in the below example.
oraext:query-database('select first_name, last_name from employees', true(),true(),'jdbc/EBS_BOLINF')
And you get the result in the XML format in the TempEmpResults variable that you can use anywhere in the process!
