Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I got the similar error, the only difference in my case is that I use tOracleSP, so the database function returns CLOB data. I went trought talend mapping file, where the specification says db CLOB to java OBJECT.
Hm, so I took a look into the source and discovered somthing strange. java.sql.Types.OTHER object is used in Talend to work with CLOB and I expect the same behavior for BLOB data types. So I did a test project directly in Eclipse test which implementation works:
_________________________________________________________________
First there is some connection needed>
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//
// or you could use a following way:
// DriverManager.registerDriver
// (new oracle.jdbc.driver.OracleDriver());
String url = "jdbc
racle:thin:@//172.28.140.110:1521/XE";
Connection conn;
conn = DriverManager.getConnection(url, "fnkvip", "oracle");
if (conn != null) {
System.out.println(" connection good");
} else {
System.out.println(" connection failed");
}
conn.setAutoCommit(false);
___________________________________________________
Ok, next I will specify what I want to call and how
// This is calling my oracle packaged function
OracleCallableStatement ocs = (OracleCallableStatement) conn
//? = call PACKAGE_NAME.FUNCTION_NAME(parameter1,parameter2,...parameterN)
// the first ? char is used in case you call a database function, which has always a return value of type Integer
.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, java.sql.Types.INTEGER);
// Return of entity primary key
ocs.registerOutParameter(9, java.sql.Types.INTEGER);
// and here finally come 2 CLOBS parameters out
// !!!!!!!! And here come CLOB in place and is the possible root cause Talend implementation, they use java.sql.Types.OTHER type to get or set
// CLOB type. When I used it in this little java project, it returned the same error as talend> "Invalid column type". But when I changed it to
// java.sql.Types.CLOB, everything worked well as bellow>
ocs.registerOutParameter(10, java.sql.Types.CLOB);
ocs.registerOutParameter(11, java.sql.Types.CLOB);
// Execute the statement
ocs.execute();
// For testing I use only the last returned parameter
Clob clobXMLFDAVKA = ocs.getCLOB(11);
// And now I want to write returned CLOB data for example into a file using stream
BufferedWriter outFile = new BufferedWriter(new FileWriter("E://FDAVKA.out"));
java.io.BufferedReader in = new java.io.BufferedReader(clobXMLFDAVKA.getCharacterStream());
char[] buffer = new char;
int bytes_read;
StringBuffer stringBuffer = new StringBuffer();
while ((bytes_read = in.read(buffer)) != -1) {
stringBuffer.append(new String(buffer, 0, bytes_read));
}
outFile.write("" + stringBuffer);
outFile.close();
// But there is also simple way supported by the driver to get directly whole string from CLOB as bellow
String myString = clobXMLFDAVKA.getSubString(1, (int) clobXMLFDAVKA.length());
The in both stringBuffer and myString variables is the same content
I already reported this behavior to Talend bugtracker at> http://talendforge.org/bugs/view.php?id=16994
Best regards,
archenroot
Hm, so I took a look into the source and discovered somthing strange. java.sql.Types.OTHER object is used in Talend to work with CLOB and I expect the same behavior for BLOB data types. So I did a test project directly in Eclipse test which implementation works:
_________________________________________________________________
First there is some connection needed>
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
//
// or you could use a following way:
// DriverManager.registerDriver
// (new oracle.jdbc.driver.OracleDriver());
String url = "jdbc
racle:thin:@//172.28.140.110:1521/XE";
Connection conn;
conn = DriverManager.getConnection(url, "fnkvip", "oracle");
if (conn != null) {
System.out.println(" connection good");
} else {
System.out.println(" connection failed");
}
conn.setAutoCommit(false);
___________________________________________________
Ok, next I will specify what I want to call and how
// This is calling my oracle packaged function
OracleCallableStatement ocs = (OracleCallableStatement) conn
//? = call PACKAGE_NAME.FUNCTION_NAME(parameter1,parameter2,...parameterN)
// the first ? char is used in case you call a database function, which has always a return value of type Integer
.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, java.sql.Types.INTEGER);
// Return of entity primary key
ocs.registerOutParameter(9, java.sql.Types.INTEGER);
// and here finally come 2 CLOBS parameters out
// !!!!!!!! And here come CLOB in place and is the possible root cause Talend implementation, they use java.sql.Types.OTHER type to get or set
// CLOB type. When I used it in this little java project, it returned the same error as talend> "Invalid column type". But when I changed it to
// java.sql.Types.CLOB, everything worked well as bellow>
ocs.registerOutParameter(10, java.sql.Types.CLOB);
ocs.registerOutParameter(11, java.sql.Types.CLOB);
// Execute the statement
ocs.execute();
// For testing I use only the last returned parameter
Clob clobXMLFDAVKA = ocs.getCLOB(11);
// And now I want to write returned CLOB data for example into a file using stream
BufferedWriter outFile = new BufferedWriter(new FileWriter("E://FDAVKA.out"));
java.io.BufferedReader in = new java.io.BufferedReader(clobXMLFDAVKA.getCharacterStream());
char[] buffer = new char;
int bytes_read;
StringBuffer stringBuffer = new StringBuffer();
while ((bytes_read = in.read(buffer)) != -1) {
stringBuffer.append(new String(buffer, 0, bytes_read));
}
outFile.write("" + stringBuffer);
outFile.close();
// But there is also simple way supported by the driver to get directly whole string from CLOB as bellow
String myString = clobXMLFDAVKA.getSubString(1, (int) clobXMLFDAVKA.length());
The in both stringBuffer and myString variables is the same content
I already reported this behavior to Talend bugtracker at> http://talendforge.org/bugs/view.php?id=16994
Best regards,
archenroot
Hi,
I solved it by setting the column to "byte[]" for Column Type and "RAW" for DB Type. Setting both schemas under tOracleOutput and tOracleInput:
Hope it helps!