oraext:query-database() is a wonderful function that can be used to
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 which your resultant output string.
3. Now in the Assign activity use this function as follows to assign the result to Schema element just created in above step.
Note: I had my SQL query in the variable SqlQuery which is being referred here.
oraext:query-database(string(bpws:getVariableData('SqlQuery')), true(), true(), 'jdbc/DataSource')
And you get the result in the XML format in the DBResult variable!
- 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 which your resultant output string.
3. Now in the Assign activity use this function as follows to assign the result to Schema element just created in above step.
Note: I had my SQL query in the variable SqlQuery which is being referred here.
oraext:query-database(string(bpws:getVariableData('SqlQuery')), true(), true(), 'jdbc/DataSource')
And you get the result in the XML format in the DBResult variable!
i have my sql query string as select max(flow_sequence) from sfo where agreement_id=34;
ReplyDeletebut 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??
Hi Pushparaj,
ReplyDeleteVerify 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.
Hi can we use ORAEXT:QUERY-DATABASE() function with INSERT query.
ReplyDelete