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:

tOracleInput
(Select from dual)
|
|
\/
tOracleInput ----> tMap ----> File
|
|
\/
tOracleBulkExec



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
tOracleBulkExec



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

thanks.
Highlighted
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();
     
       if(result.next()){
       
        result_int=result.getInt("maxval");
           
       }else{
        //System.out.println("no entra");
       }

       result.close();
       preStatement.close();

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

return new BigDecimal(result_int);

}
   }

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Have you checked out Talend’s 2019 Summer release yet?

Find out about Talend's 2019 Summer release

Blog

Talend Summer 2019 – What’s New?

Talend continues to revolutionize how businesses leverage speed and manage scale

Watch 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

Blog