Oracle "Invalid Column Type"

One Star

Oracle "Invalid Column Type"

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?
Community Manager

Re: Oracle "Invalid Column Type"

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Oracle "Invalid Column Type"

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

Re: Oracle "Invalid Column Type"

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Oracle "Invalid Column Type"

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?
Community Manager

Re: Oracle "Invalid Column Type"

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
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Oracle "Invalid Column Type"

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

Re: Oracle "Invalid Column Type"

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

Re: Oracle "Invalid Column Type"

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?
One Star

Re: Oracle "Invalid Column Type"

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
Six Stars

Re: Oracle "Invalid Column Type"

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 = "jdbcSmiley Surprisedracle: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
One Star

Re: Oracle "Invalid Column Type"

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
One Star

Re: Oracle "Invalid Column Type"

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
Four Stars

Re: Oracle "Invalid Column Type"

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