I'm trying to use SQL within a Java transformation, something like "SELECT SEQUENCE_NAME.nextval FROM DUAL". I will need to call it multiple times (up to a few thousand times) in order to synchronize sequences from one environment to another. Although I have completed a small stored procedure, I would like to do it entirely within a mapping without the stored procedure. Any ideas how to approach this?
I'll need to run the SQL multiple times. So, if the sequence value is 30 in my environment, but 45 in the other environment, I'll need to run "SELECT SEQUENCE_NAME.nextval FROM DUAL" 15 times. I want to synch the sequences from one environment to another.
It looks like you are using Oracle. Do you have alter access to the sequence. You can get the current value (30) from your environment and the current value (45) from your other environment and get the difference (15) in an expression and execute the SQL in a SQL transformation passing in the number dynamically.
"alter sequence SEQUENCE_NAME increment by 15;"
Or the reverse.
"alter sequence SEQUENCE_NAME increment by -15;" to change it on your environment.
To execute SQL in the java transformation, you will need to at the Oracle driver to the classpath in the session to connect to the database and execute the SQL.
Although following solution is not tailor made for you, it should give you enough ideas on how to execute SQL from Java Transformation.
In addition to this, you will also find over 100 mapping samples on Informatica Marketplace website.