oraext:query-database() is a wonderful function that can be used to
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!
- 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.
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!
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??
I tried the same with count() func and it doesn't work either.
DeleteHi 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.
ReplyDeleteHi Ankit,
ReplyDeleteI 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
Hi Neeraj,
ReplyDeletegood 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
when doing same thing in xslt (transformation) getting following error
ReplyDeleteA Simple Content can only be Mapped to a Simple or Mixed Content Type Element
Thank you so much! Worked like a charm!!
ReplyDeleteHi can we use ORAEXT:QUERY-DATABASE() function with INSERT query?.If yes, then how can we do?
ReplyDeleteassign 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