TrImport – Trim your insert statements

Did you ever want to copy data from database to another and want to exclude certain columns? With the basic export utility in SQL Developer one can specify a selection criteria (effectively a where clause) to export certain rows. It will produce an SQL script with insert statements. However, virtual, or calculated, columns are also included, even though their data can be derived. This is a problem only when attempting to run the script against the target database, it will not allow you to perform an insert on a virtual column. It is my understanding that this is to be addressed in a later Oracle Database 11g release. Until then, what are you to do?

Well, you could use Oracle Data Pump to export only the specific columns. However, in my case, I had the export SQL scripts to start with, provided my someone else, and not the original DB.

I put together a very simple Java Swing application (called TrImport) which allows you to remove columns, and their corresponding values, from an insert statement in an SQL script.

With TrImport now you can easily change the following SQL statement

insert into TABLE_A set (ROW_ID, NAME, DESC, NAME_VC) values (456, ‘Record Two’, ‘This is a record’, ‘record two’);

to this

insert into TABLE_A set (ROW_ID, NAME, DESC) values (456, ‘Record Two’, ‘This is a record’);

with ease. Obviously the benefit is not that this can be done with just one insert statement, but all of the insert statements in a file.

The steps are really simple:
1. Open the file with you SQL script. This can be a mixed script with inserts, updates, comments, just about anything.
2. Open the columns window to see the tables and columns that TrImport has identified in any insert statements it has found.
3. Tick the columns you want to exclude and press OK.
4. Save the file to a new location.

TrImport is not just for virtual columns either, it can also be used in a situation where the table in the destination database has fewer columns.

To get TrImport and run it simply download the trimport-0.1.jar and from the command line execute ‘java -jar trimport-0.1.jar’. If you wanted to, you could setup a short cut. TrImport is provided without any support. It has only been tested with a small set of SQL scripts and only with one function, that is to_timestamp.

Broadband in India

Normally I would not provide a link to a site without a privacy policy. However, for India Broadband Forum I will make an exception with a warning: do not post, just browse for information to see what others are saying about broadband providers in India. The reason I’m doing this is to highlight the huge differences in services provided in different regions. This is important particularly if you are relying on staff in India to work from home at times that overlap with Eastern or Pacific timezones.

You could use the World Speed Test to compare download and upload speeds between regions, cities and service providers in India. Mumbai (10.08 Mbps max) is better off than Madra (9.09 Mbps max), which in turn is better off than Bangalore (7.21 Mbps max). This, however, only gives you part of the picture. The forum, with it’s groups and blogs can give you better insight into user’s experiences with reliability, cost and support. Fast internet access removes one of the barriers to effective distributed teams.

Coherence failed to load the factory

Recently I’ve been working on a set of applications which are deployed in their own, separate OC4J instances in a cluster. Many of these applications share data and Coherence is used to provide synchronised data caching between the OC4J instances.
Normally this is straight forward enough, except that when I went to set up another environment I started getting failures at startup.
The logs would show messages like…

getConfigurableCacheFactory “Failed to load the factory”

and

Caused by: (Wrapped: Failed to load the factory) java.lang.reflect.InvocationTargetException
at com.tangosol.util.Base.ensureRuntimeException(Base.java:286)
at com.tangosol.net.CacheFactory.getConfigurableCacheFactory(CacheFactory.java:607)
at com.tangosol.net.CacheFactory.getCache(CacheFactory.java:686)
at com.tangosol.net.CacheFactory.getCache(CacheFactory.java:664)

Coherence generally has nice, helpful, error messages which point you in the right direction to solve the problem. This time however, all we got is a ‘Failed to load the factory’ message which, while accurate, does not give an indication about how to fix things.
After some verification in my local development environment I determined that the above message occurs if the coherence cache config file can not be found in the location specified, or if the config file XML is not well formed. In other words, the factory can not be loaded if the cache config file can not be found or parsed.

When is a string literal too long?

When it’s a multi-byte encoded string, that’s when.

Recently I came across a runtime problem where an ORA-01704 (string literal too long) error was occuring when inserting some text into a database table. The column had been set to the maximum amount of characters allowed for VARCHAR2, that is 4000 characters. Before inserting any values, the text was put through a truncation method to make sure it did not exceed 4000 characters. That’s what made the issue such a conundrum. If we had code limiting the string to 4000 characters, and the logs for the SQL statement were showing that the truncation to 4000 characters was happening, why was the Oracle database reporting that the string was longer than 4000 characters?

This is where the logs became very important. I noticed that the some of the text contained non-Western European characters, which was perfectly valid. However, while these characters are counted as single characters, depending on the encoding, they could be multi-byte characters. Not only is 4,000 the maximum number of characters for VARCHAR2, it is also the maximum number of bytes. In our case, we had a string of 4,000 characters, but in UTF-8 it required 4,017 bytes to store it. So, the solution was to make a slight change to the truncation logic, by introducing a maximum number of bytes for a specific character encoding. Now the truncation method has the following:


try {
while (truncatedString.getBytes(charSetName).length > maxBytes) {
 // remove the last character and check the bytes lenght again
 truncatedString = truncatedString.substring(truncatedString.length() - 2);
}
} catch (UnsupportedEncodingException e) {
logger.log(Level.SEVERE, "The character set " + charSetName +
" is not supported. Unable to truncated string if it is too long.", e);
}

The variable charSetName is set to the character set used by the database (in our case UTF-8) and the maxBytes variable is the maximum number of bytes allowed (in our case 4000). In the rare cases when there are over 4000 bytes in the string, this code will systematically remove the last character until the number of bytes come down. This code could be improved to be more efficient. For example, the code could check how many characters need to be removed and then remove that chunk, rather than removing a character at a time. It performs in the scenario it is used because the string is already chopped down to 4,000 characters. If the system begins to receive a large amount of multi-byte characters in a single string, then reducing the string to 4,000 characters may still leave you with an 8,000 byte string. Let me know if you determine a more efficient approach.

Use Social Networks for Competitive Intelligence

In a recent article, Outward Insight’s Ken Sawka examines how Twitter, LinkedIn and other social networking Web sites and tools have enormous potential for establishing a network of competitor information sources.

In combination with other primary and secondary information sources, LinkedIn can contribute to locating and communicating with business professionals that have the knowledge and expertise to address your company’s competitive intelligence needs.
…
Competitive intelligence analysts can similarly use wikis to collaborate with CI function practitioners and contributors to define intelligence requirements, share observations and information, and test and debate analytic hypotheses and conclusions.

Apart from reaching out to customers and potential customers, Kenneth makes the point that social networking can dramatically increase the value and impact of competitive intelligence inside their organisation.

Kenneth Sawka is the managing partner of competitive intelligence and strategy consulting firm Outward Insights in suburban Boston. Outward Insights contributes a monthly column on competitive intelligence issues to Kiplinger Recommends. Sawka has more than 20 years’ of business and government intelligence experience and has appeared on CNBC’s Squawk Box and in publications such as Time and Investor’s Business Daily.