In this blog, I am going to show the way to tackle dynamic 'where' condition in the function orcl: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")
Bingooooooooo....
Happppy Learning....
11 comments:
Hi Abhi,
I'm new to Oracle SOA Suite. I'm using ESB for A2A. During the mapping of source to target, I have a requirement to populate sequence number to a field. I decided to use 'sequence-next-val' function. I followed the syntax(orcl:sequence-next-val('seqname','dbconnection info')) which is given in the help. I entered the syntax in the 'sequence' parameter in the dialogue window. When I try to save, it throws an error as 'sequence-next-val: Error: Function with Too Few Parameters'. Please help me on this with a simple example.
Regards,
sen
Hi sen,
The function orcl:sequence-next-val takes 2 parameters. It return the next value of an Oracle sequence. It is similar to sequence generation what we see in Oracle sql.
How to proceed:
First of all you need to drap that function to the tranformation swimlane. Create a database sequence, say,TEST. This will be the first parameter for the function.
The second parameter will be your database connection details, i.e. the database in which your source table is defined.
Suppose your database is having details as:
Username: abhi
Password: abhishek
Host: 172.128.5.13
Port: 1521
SID: Oracle
so the second parameter will look like this:
'jdbc:oracle:thin:abhi/abhishek@172.128.5.13:
1521:Oracle'
or for the second parameter you can add a datasource entry in data-sources.xml file under soa_home\j2ee\oc4j_soa\config.
Say the datasource name is BPEL_DBS. so the function will look like,
orcl:sequence-next-val('TEST','jdbc/BPEL_DBS')
After doing this, map it to the destination field.
I have just posted my comment related to this topic, as a post.
Hi Abhishek,
We are trying to query data from a table and would like to use the rowsetfeature. Seeing the use of rowset in you're transformation - I was wondering if its possible to do it directly using the Select of the DB Adapter. Any ideas would help,
HI Abishek,
i am trying to query database by passing a number from input xml.When i use concat and pass value from my input xml in database query, it throws me error saying its "invalid number" in em while execution.
using this sql query works fine in toad
SELECT UIDMETER FROM PWRLINE.METER WHERE METERID = '613598'
but number without quotes doesnot work. Gives "Invalid number" in toad
SELECT UIDMETER FROM PWRLINE.METER WHERE METERID = 613598
Please advise
Hi Santosh,
Try to type cast it before passing. Use appropriate xpath function present under conversion functions.
It will work fine.
Cheers,
Abhi...
Hi,
I'm getting: "XPathExecutionError" when trying to assign: oraext:sequence-next-val('sqeName', 'eis/DB/XXX').
Any Ideas? I have in my bpel decleration: xmlns:oraext="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.ExtFunc"
Thanks
Riko
Hi Riko,
I can see the possible reason for error. The value provided by you to the function is not proper. You are trying to pass the JNDI name to the second parameter.
You should pass the datasource name as jdbc/XXXXX to the function.
Try it out, hope it works for you.
Cheers,
Abhishek
Hi Abhishek,
I m new to oracle SOA, how to handle node-set if multiple records are returned by query-database function
Hi
Can i use this function to update data in the table?
Thanks
i've a date in the format 01-FEB-2016
ive to convert it to yyyymmdd same as you have given in your blog.
but the date is in a variable but whenever i run the query with the following function:
oraext:query-database("select to_char(to_date('$date_Var', 'DD-MON-YYYY'), 'YYYY/MM/DD') from dual",false(),false(),"jdbc/XXERPCloud")
im getting an error:
Error invoking 'queryDatabase':'oracle.xml.sql.OracleXMLSQLException: ORA-00911: invalid character
'.
Post a Comment