Wednesday, August 6, 2008

Using "orcl:query-database" in transformation

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:

Sen-SOA said...

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

Abhishek Saurabh said...

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.

Abhishek Saurabh said...

I have just posted my comment related to this topic, as a post.

Vidya Mani said...

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,

santosh said...

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

Abhishek Saurabh said...

Hi Santosh,

Try to type cast it before passing. Use appropriate xpath function present under conversion functions.

It will work fine.

Cheers,
Abhi...

Riko said...

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

Abhishek Saurabh said...

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

Tehseen said...

Hi Abhishek,

I m new to oracle SOA, how to handle node-set if multiple records are returned by query-database function

Nupur said...

Hi

Can i use this function to update data in the table?

Thanks

wasil toufiq said...

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
'.