Wednesday, August 13, 2008

Using orcl:sequence-next-val

Recently this question was asked to me as a comment to one of my post. I have replied to the comment. I also felt to post this as a blog so that it can prove beneficial to others. Here it goes...

INTRODUCTION:
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 drag that function to the tranformation swimlane.
Create a database sequence in Database, say,TEST. This will be the first parameter for the orcl:sequence-next-val 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.

Your are done...Happy Learnings...

3 comments:

Naveen said...

is there a way to get sequence current value like orcl:sequence-curr-val(.........)

Unknown said...

Can u please clearly create a project using this seq-next-val
consider like emp_id should be generated using sequence

Unknown said...

Hi Abhishek,

Is there a function to reset the value of the sequence? I have tried searching but could not find any.

Thanks
Pulkit