Working with CLOB/BLOB data types - old Talend issue

Six Stars

Working with CLOB/BLOB data types - old Talend issue

In my scenarion I use tOracleSP used to call oracle function. One of the returned parameter is type of CLOB on the database side. When I use type String for saving CLOB value into it, it is ok, but the variable is empty. But based on information bellow I schould use Object instead of String data type. In that case (Object) error occurs:
Starting job Testovaci_Vygeneruj_FDAVKU at 12:08 09/11/2010.
connecting to socket on port 3546
connecting to socket on port 4546
Exception in component tOracleSP_1
java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.DatabaseError.throwSqlException(
at oracle.jdbc.driver.DatabaseError.throwSqlException(
at oracle.jdbc.driver.DatabaseError.throwSqlException(
at oracle.jdbc.driver.OracleStatement.getInternalType(
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(
at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(
at fnkv_ip.testovaci_vygeneruj_fdavku_0_1.Testovaci_Vygeneruj_FDAVKU.tFixedFlowInput_1Process(
at fnkv_ip.testovaci_vygeneruj_fdavku_0_1.Testovaci_Vygeneruj_FDAVKU.tOracleConnection_1Process(
at fnkv_ip.testovaci_vygeneruj_fdavku_0_1.Testovaci_Vygeneruj_FDAVKU.runJobInTOS(
0|5D335BE1F6FB955683BF7125C5618E9B|FN KRÁLOVSKÉ VINOHRADY ZEMAN Marek, MUDr.|Fakultní nemocnice|ZPMV ?R Pobo?ka PRAHA a ST?EDNÍ ?ECHY|U Výstavi?t? 287 / 17|Praha 7 - Hole?ovice|170 00|200062||
at fnkv_ip.testovaci_vygeneruj_fdavku_0_1.Testovaci_Vygeneruj_FDAVKU.main(
Job Testovaci_Vygeneruj_FDAVKU ended at 12:08 09/11/2010.
Also the strange look of stack output appears always. There are output mixed with exception messages.
I found similar topics over the forum, but there is no solution of these issues:
I am able to find in menu Window -> Preferences -> Talend -> Specific Settings -> Metadata of TalendType the record "mapping_Oracle.xml". Based on information from this file I found something strange:
In case of Java language there is defined following statement:
<language name="java">
<talendType type="id_Object">
<dbType type="BLOB" default="true"/>
<dbType type="CLOB"/>
<dbType type="BFILE"/>
<dbType type="XMLTYPE"/>
<dbType type="ROWID"/>
<dbType type="UROWID"/>
<dbType type="CLOB">
<talendType type="id_Object" default="true" />
So the mappings is O.K. CLOB schould be retrieved from database as Object java type. I cannot say if default java Object type is able to keep this type of data. Is it?
What I though about the possible solution:
Call database function/procedure inside of Talend routine and when dealing with CLOBs database type use "java.sql.Clob". Something like following code:
public static String CLOBToString(){
// or
// DriverManager.registerDriver
// (new oracle.jdbc.driver.OracleDriver());
String url = "jdbcSmiley Surprisedracle:thin:@//server.local:1521/prod";
// jdbcSmiley Surprisedracle:thin:@//hostSmiley Tongueort/service
// or
// String url = "jdbcSmiley Surprisedracle:thin:@server.local:1521Smiley Tonguerodsid";
// jdbcSmiley Surprisedracle:thin:@hostSmiley TongueortSmiley FrustratedID
// SID - System ID of the Oracle server database instance.
// By default, Oracle Database 10g Express Edition
// creates one database instance called XE.
// ex : String url = "jdbcSmiley Surprisedracle:thin:@myhost:1521:xe";
Connection conn = DriverManager.getConnection(url,"scott","tiger");
Statement stmt = conn.createStatement();
// Select LOB locator into standard result set.
ResultSet rs = stmt.executeQuery ("SELECT blob_col, clob_col FROM lob_table");
while (
// Get LOB locators into Java wrapper classes.
java.sql.Blob blob = (java.sql.Blob)rs.getObject(1);
java.sql.Clob clob = (java.sql.Clob)rs.getObject(2);

OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call ReturnCLOB()}");
ocs.registerOutParameter(1, OracleTypes.CLOB);
oracle.sql.CLOB clob = ocs.getCLOB(1);
There is also question about using java.sql or oracle.sql type library?
As soon as I wasn't able to see any working solution about working with CLOBs neither BLOBs in Talend, I thank you for any response.
Six Stars

Re: Working with CLOB/BLOB data types - old Talend issue

I spent again some time with seeking the solution for component tOracleSP (even other components) work with CLOB, but without success. Then I wrote small piece of code which is able to write returned CLOB data to file. I will of course edit it to make it able work as Talend routine with some In/Out parameters.
Here is the current working code in Eclipse (you need Oracle JDBC drivers which provides advanced functions agains standard JDBC which can be downloaded here, as soon as I have some working example for Talend, I will post it here too:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
public class testhash {
public static void main(String[] args) throws IOException,
NoSuchAlgorithmException, SQLException {
try {
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
// or
// DriverManager.registerDriver
// (new oracle.jdbc.driver.OracleDriver());
String url = "jdbcSmiley Surprisedracle:thin:@//";
Connection conn;
conn = DriverManager.getConnection(url, "fnkvip", "oracle");
if (conn != null) {
System.out.println(" connection good");
} else {
System.out.println(" connection failed");
// This is calling my oracle packaged function
OracleCallableStatement ocs = (OracleCallableStatement) conn
//? = call PACKAGE_NAME.FUNCTION_NAME(parameter1,parameter2,...parameterN)
.prepareCall("{? = call ROZHRANI_S_POJ.VRAT_XML_FDAVKU(?,?,?,?,?,?,?,?,?,?)}");
// Set up parameters, each char '?' in prepareCall has its index number, based on this
// , I will work with these as input or output parameters

// Set up input parameters
ocs.setString(2, "5D335BE1F6FB955683BF7125C5618E9B");
ocs.setString(3, "FN KRÁLOVSKÉ VINOHRADY ZEMAN Marek, MUDr.");
ocs.setString(4, "Fakultní nemocnice");
ocs.setString(5, "ZPMV ?R Pobo?ka PRAHA a ST?EDNÍ ?ECHY");
ocs.setString(6, "U Výstavi?t? 287 / 17");
ocs.setString(7, "Praha 7 - Hole?ovice");
ocs.setString(8, "170 00");

// Output parameters
// Each function on Oracle database has its return value, and this is it
ocs.registerOutParameter(1, OracleTypes.NUMBER);
// Return of entity primary key
ocs.registerOutParameter(9, OracleTypes.NUMBER);
// and here finally come 2 CLOBS parameters out
ocs.registerOutParameter(10, OracleTypes.CLOB);
ocs.registerOutParameter(11, OracleTypes.CLOB);
// Execute the statement

oracle.sql.NUMBER numberPkFaktura = ocs.getNUMBER(9);
oracle.sql.CLOB clobFDAVKA = ocs.getCLOB(10);
// For testing I use only the last returned parameter
oracle.sql.CLOB clobXMLFDAVKA = ocs.getCLOB(11);

// And now I want to write returned CLOB data for example into a file
BufferedWriter outFile = new BufferedWriter(new FileWriter("E://FDAVKA.out")); in = new;
char[] buffer = new char;
int bytes_read;
StringBuffer stringBuffer = new StringBuffer();
while ((bytes_read = != -1) {
stringBuffer.append(new String(buffer, 0, bytes_read));
outFile.write("" + stringBuffer);
Best regards,
Six Stars

Re: Working with CLOB/BLOB data types - old Talend issue

I created bug report at related to this issue


Talend named a Leader.

Get your copy


Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables


Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema


Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables