topic Re: Oracle "Invalid Column Type" in Talend Studio

Oracle "Invalid Column Type"

Anonymous — Sat, 16 Nov 2024 13:45:06 GMT

When trying to do a simple tOracleInput -> tMap -> tOracleOutput flow, I'm getting the following error:
Exception in component tOracleOutput_1
java.sql.SQLException: Invalid column type

I've made sure that the source schema definition is exactly the same as the target schema definition (by pressing "Sync Columns"), but the error continues.
I'm wondering if the JAVA - Object --> Oracle Varchar2 mapping is causing the problem. Anyone experience the same thing?

Re: Oracle "Invalid Column Type"

Anonymous — Sun, 27 Sep 2009 03:50:43 GMT

Hello

I'm wondering if the JAVA - Object --> Oracle Varchar2 mapping is causing the problem. Anyone experience the same thing?

From your image, I see all the datatype mapping are right.
JAVA - Object mapping Oracle BLOB/CLOB
JAVA -String mapping Oracle char/varchar2
You can go to preferences-->Talend-->Specific Setting--.>Metadata of TalendType -->mapping_Oracle.xml and see the datatype mapping.
Best regards

shong

Re: Oracle "Invalid Column Type"

Anonymous — Sun, 27 Sep 2009 07:00:36 GMT

If the mappings are correct, what am I to do? It sounds like I shouldn't be getting this error. Any suggestions?

Re: Oracle "Invalid Column Type"

Anonymous — Sun, 27 Sep 2009 07:26:14 GMT

Hello
Please show me the whole error trace, i don't know which line of code throws error. Can you export the job and send it to via email? I will debug the job.
Best regards

shong

Re: Oracle "Invalid Column Type"

Anonymous — Sun, 27 Sep 2009 07:46:21 GMT

Hi, here is the full message:
Exception in component tOracleOutput_1
java.sql.SQLException: Invalid column type
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3433)
at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4261)
at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4250)
at migrate_stage_to_production.incorrect_mapping_0_1.incorrect_mapping.tOracleInput_1Process(incorrect_mapping.java:2270)
at migrate_stage_to_production.incorrect_mapping_0_1.incorrect_mapping.runJobInTOS(incorrect_mapping.java:2591)
at migrate_stage_to_production.incorrect_mapping_0_1.incorrect_mapping.main(incorrect_mapping.java:2471)
Job incorrect_mapping ended at 00:38 27/09/2009.

How do you want me to export the job? Do I export as a war? Any autonomous job?

Re: Oracle "Invalid Column Type"

Anonymous — Sun, 27 Sep 2009 08:40:38 GMT

Hello guy

How do you want me to export the job? Do I export as a war? Any autonomous job?

Right click on the job name and select 'export job scripts' option to export a autonomous job. Aslo, please tell me which version of TOS do you use?
Best regards

shong

Re: Oracle "Invalid Column Type"

Anonymous — Wed, 07 Oct 2009 22:39:09 GMT

SHONG -
I sent you the file. Perhaps I should send it again?

Re: Oracle "Invalid Column Type"

Anonymous — Thu, 08 Oct 2009 15:08:07 GMT

For your BLOB/CLOB fields try using a Byte[] array instead of Object for Java type.

Re: Oracle "Invalid Column Type"

Anonymous — Thu, 08 Oct 2009 17:53:27 GMT

I tried this and still get "java.sql.SQLException: Invalid column type: sqlType=2003". Would you be willing to show some screenshots of a successful byte[] to CLOB/BLOL mapping?

Re: Oracle "Invalid Column Type"

Anonymous — Thu, 11 Mar 2010 10:36:13 GMT

This seems to be a common problem, I'm trying to insert data from a text file into a CLOB on Oracle and get the same error, I can't find any solution on the forums. Talend (3.2.0)
#Edit
I am using the following workaround for importing a text file (comma delimited). I make it look at each file as a single line, then insert the line:
Add a delimiter in front of the very first line (I'm using /#).
Then read the file as a delimited file using the above delimiter as the row delimiter.
This seems to work. The tMap field is object and loads into a CLOB on Oracle.
Regards

Re: Oracle "Invalid Column Type"

Anonymous — Fri, 12 Nov 2010 12:12:53 GMT

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

Re: Oracle "Invalid Column Type"

Anonymous — Tue, 15 Feb 2011 11:16:21 GMT

Hi,
Can you tell us what all the types of delimiters used in filed separator and Row Separator, for data migration purpose
Scenario: View to Flat file formats(CSV,xcel)
Regards,
Sukesh

Re: Oracle "Invalid Column Type"

Anonymous — Thu, 13 Feb 2014 16:29:44 GMT

Hi every body
I have a project in Talend and I cannot understand this error :
"java.sql.SQLException: Invalid column type: sqlType=2003"
Any help please ! thank you

Re: Oracle "Invalid Column Type"

Anonymous — Tue, 04 Apr 2017 19:42:07 GMT

Hello All,
I am getting same error. Did anyone got any resolution.

Exception in component tOracleOutput_2
java.sql.SQLException: Invalid column type: sqlType=2003
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229)
at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4405)
at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4293)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1280)
at local_project.ods_distributor_master_0_1.ODS_Distributor_Master.tOracleInput_2Process(ODS_Distributor_Master.java:16863)
at local_project.ods_distributor_master_0_1.ODS_Distributor_Master.runJobInTOS(ODS_Distributor_Master.java:35368)
at local_project.ods_distributor_master_0_1.ODS_Distributor_Master.main(ODS_Distributor_Master.java:35182)
Thanks,
Abhijit

Re: Oracle "Invalid Column Type"

Anonymous — Wed, 16 Oct 2019 11:32:05 GMT

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!