Monday, August 22, 2011

What service has oci.dll open?

Attempting to use deployment procedure's to install patch bundles on windows hosts, sometimes you run into issues with services keeping dll's (especially oci.dll) open.

Here's a trick to determining what service is keeping a specific dll open.

From a command prompt:

tasklist /m > tasks.txt
notepad tasks.txt

The look for the specified dll in tasks.txt and find the process. From there it should be somewhat obvious which service to shut down in order to get the patch to continue.

Tuesday, August 2, 2011

ORA-01406: fetched column value was truncated

I just got a dump file of our data from a vendor that is hosting an application for us and I was making it available to my developers and since we will be getting files on a somewhat frequent basis, initially I created an external table to read the data.

I had to get over the first hurdle of version incompatibility of the datapump file, which wasn't too bad, but after I cleared up those issues (lots of references on google, this chart helped the most) I received the following error in Toad:

ORA-01406: fetched column value was truncated

with further details
fetched column value was truncated

Cause: In a host language program, a FETCH operation was forced to truncate a
character string. The program buffer area for this column was not large enough to
contain the entire string. The cursor return code from the fetch was +3.

Action: Increase the column buffer area to hold the largest column value or perform
other appropriate processing.

Google wasn't as helpful with this (hence the post here), but essentially this is a client side setting that needs to be adjusted. In version of Toad there is one setting that needs to be changed. Search for "OCI Array Buffer size" or find it under "Oracle - General". You need to change it to be large enough to fetch the largest column in the query. In my case I had a column of size 4000, so setting this to 4000 returned results.