How to use Oracle sequence and tOracleBulkExec

One Star

How to use Oracle sequence and tOracleBulkExec

Using Talend Studio on Windows and Oracle DB.

Have read post about using the Advanced Tab on the tOracleOutput, but would like to use bulk operations.

Kinda of new to Talend. Is there a way to execute

SELECT mysequence.nextval from dual

For each Row of another query?

tMap seems to only want to make the call once for the sequence. I've tried something like:

(Select from dual)
tOracleInput ----> tMap ----> File

Or is there a way via tInterate or TForEach to make successive calls to the SELECT FROM DUAL and then Merge it with another record set. Put that all in a file and use BULK. Like:

/ tInterate ---> tOracleInput (SELECT sequence from DUAL)
tOracleInput ---> tReplicate / |
| \ |
| \ \ /
| \ --->----> ----> tMap ---- tFileOutDelimited

If this is covered somewhere else on Forum. My apologies. Please re-direct me.

One Star

Re: How to use Oracle sequence and tOracleBulkExec

I use a code routine in Tmap to get ids from a sequence.

package routines;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;

 * user specification: the function's comment should contain keys as follows: 1. write about the function's comment.but
 * it must be before the "{talendTypes}" key.
 * 2. {talendTypes} 's value must be talend Type, it is required . its value should be one of: String, char | Character,
 * long | Long, int | Integer, boolean | Boolean, byte | Byte, Date, double | Double, float | Float, Object, short |
 * Short
 * 3. {Category} define a category for the Function. it is required. its value is user-defined .
 * 4. {param} 's format is: {param} <type>[(<default value or closed list values>)] <name>[ : <comment>]
 * <type> 's value should be one of: string, int, list, double, object, boolean, long, char, date. <name>'s value is the
 * Function's parameter name. the {param} is optional. so if you the Function without the parameters. the {param} don't
 * added. you can have many parameters for the Function.
 * 5. {example} gives a example for the Function. it is optional.
public class GenerateID {

     * getId: not return value, only print "hello" + message.
     * {talendTypes} String
     * {Category} User Defined
     * {param} 
     * {example} helloExemple("world") # hello world !.
     * @throws SQLException 

public static BigDecimal getId(Object connection_) throws SQLException {

Integer result_int=0;

try {

Connection connection=(Connection )connection_;

if (connection != null) {

String sql ="SELECT BDE_SEQ.NEXTVAL as maxval FROM DUAL";

       //creating PreparedStatement object to execute query
       PreparedStatement preStatement = connection.prepareStatement(sql);
       ResultSet result = preStatement.executeQuery();
        //System.out.println("no entra");


} else {
System.out.println("Failed to make connection!");
}catch(Exception e){

return new BigDecimal(result_int);



Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend


Downloads and Trials

Test drive Talend's enterprise products.