Monthly Archives: December 2012

Outsourcer 3.0.1

  • Fix for Oracle dates in which the time was being truncated that started with version 2.7.  With 2.7, I added code to handle two digit years which Oracle uses for centuries between 0 and 100 but I used getDate instead of getTimestamp.
  • Fix for Oracle JDBC driver mismatch which may cause ArrayIndexOutOfBoundsException when using “getString”.
  • Fix for double quotes around object name in function os.fn_create_ext_table.  If you already upgraded to 3.0 and have executed the upgrade_3_0.sql file, please recreate this function with this SQL file.  If you are upgrading from 2.x, be sure to execute the upgrade_3_0.sql file.

Link to Outsourcer 3.0.1 => os_3_0_1

 

Interesting Limit to Inserts with Java

Outsourcer uses Java and in doing so, the INSERT statement uses the Statement Class and the executeUpdate method.  This method can be seen here.

The executeUpdate is used for INSERT, UPDATE, or DELETE and returns the number of rows impacted by the statement as an int.  In Java, an “int” is defined with a maximum of 2,147,483,647.  This means if your SQL statement affects more than 2.1 Billion rows, then Java will throw an error.  Java int documentation here.

A Greenplum customer using Outsourcer recently contacted me about this error message:

Unable to interpret the update count in command completion tag: INSERT 0 3913383319

This is the error message you will get from Java when you try to insert more than 2.1 Billion rows.  In this case, the customer inserted 3.9 Billion rows from Oracle using Outsourcer.

If you hit this limit in Outsourcer, don’t worry.  The rows actually get inserted and all you need to do is ANALYZE the table.  It is an odd limit that can be avoided.  You can use a VIEW in the source to break up the source table into smaller parts or you could use the External Table feature in Outsourcer.  You can also just ignore this error because the data does get inserted.