Using "orcl:query-database" in BPEL Transformation or Assign
In this blog, I am going to show the way to tackle dynamic 'where' condition in the functionorcl:query-database. It returns a node-set by executing the SQL query against the specified database.
Just a brief introduction about how this function works:-
Signature:
orcl:query-database(sqlquery as string, rowset as boolean, row as boolean, datasource as string)
Arguments:
sqlquery – The SQL query to perform.
rowset – Indicates if the rows should be enclosed in a <rowset> element.
row – Indicates if each row should be enclosed in a <row> element.
datasource – Either a JDBC connect string (jdbc:oracle:thin:username/password@host:port:sid) or a JNDI name for the database.
Example1:
orcl:query-database("select empno from emp
where empno='50'",false(),false(),"jdbc/DbConnection")
Now coming back to the main agenda about giving dynamic value to the where clause. Its pretty simple. Just a little bit trick.
Use the function with the concat function, as:
Example2:
orcl:query-database(concat("select empno from emp where empno=",/tns:Root-Element/tns:Data/:tns:Empno),false(),false(),"jdbc/DbConnection")
