Tuesday, April 5, 2011

How to get XML output from the oraext:query-database() function

oraext:query-database() is a wonderful function that can be used to
  1. Fetch the data in the Assigns/Transformations on the fly without the use of DB Adapter
  2. 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!
   

10 comments:

  1. i have my sql query string as select max(flow_sequence) from sfo where agreement_id=34;

    but is giving error as
    0XPath expression failed to execute. An error occurs while processing the XPath expression; the expression is oraext:query-database(concat('select max(flow_sequence) from sfo_agreement_lines where agreement_id=',$agreement_id),false(),false(),'jdbc:oracle:thin:fusion/fusion@indl68095.idc.oracle.com:1522:in68095'). The XPath expression failed to execute; the reason was: internal xpath error. Check the detailed root cause described in the exception message text and verify that the XPath query is correct.

    any inpus on this??

    ReplyDelete
    Replies
    1. I tried the same with count() func and it doesn't work either.

      Delete
  2. Hi Pushparaj,

    Verify 3 things.
    1. Create the entire query first and then pass it to query-database function
    2. Have getConetentAsString function wrapping the query-DB function and check if the string results.
    3. Verify the jdbc URL for correctness.

    ReplyDelete
  3. Hi can we use ORAEXT:QUERY-DATABASE() function with INSERT query.

    ReplyDelete
  4. Hi Ankit,

    I am getting the null fault issue when i am copying the data to target variable . Looks like it is the issue with namespace. Can you please help me here.

    Thanks

    ReplyDelete
  5. Hi Neeraj,

    good Article, but i have one Problem. Can you detailed explain the
    Content of your TempEmpResults Variable.
    In my Application i cannot transform the Result of query-database to
    my Response Variable.

    Thanks
    Stefan

    ReplyDelete
  6. when doing same thing in xslt (transformation) getting following error

    A Simple Content can only be Mapped to a Simple or Mixed Content Type Element

    ReplyDelete
  7. Thank you so much! Worked like a charm!!

    ReplyDelete
  8. Hi can we use ORAEXT:QUERY-DATABASE() function with INSERT query?.If yes, then how can we do?

    ReplyDelete
  9. assign activity getting failed when the result value is null if database column is empty while using this function.. getting error from-spec is empty or null. how to accept null value while using this function ORAEXT:QUERY-DATABASE()?

    ReplyDelete