DSXchange

I have two jobs both using SELECT in an Oracle Connector stage as the source. One works fine but the other gives the error:
"the connector received Oracle error code ORA-1406"

The job works fine with Oracle Enterprise stage.

The NLS values are the same for both. Also, I checked with the DBAs to see if the characteristics of the two underlying tables are the same and they are. I also went through all posts. There are some patch numbers people have mentioned and one post mentions installing FP2 for 8.1. I would think that if there is an issue it should happen with both jobs. I also tried looking at the Oracle column value to see if it has any hidden characters and it doesn't.

Here are some stuff from some posts:

1)Using NVarchar sql type. Result: Did not solve the problem

2)Using APAR# JR36822 and/or APAR# JR39895 Result: Did not try yet. I am assuming that I get this from IBM.

3)We had to delete "NLS_DMU_USAGE" from v$nls_parameteres.
Result: We don't have this setting.

4)Difference between tables. Character type or byte type. Result: Not sure how to check for this.

Any ideas?

Hi,

Are you able to do a "View Data"

Is it the same issue as

viewtopic.php?p=406950

Regards,
Manu

It is the same issue. I went through that post as well. I cannot do a view data.

This error is reported in the director after I run the job.

So, two different tables and one throws the truncation error or two different jobs selecting from the same table where one works and one doesn't?

Assuming the former, do your table have any "unbound" numbers (without a precision specified) or LOB columns? Those are typically involved in a 1406 error.

Yes, it is the former.

The table has 494 columns. 2 of them are numbers defined as number(10) and 2 are dates, the rest are varchars.

The first column where the problem starts is varchar2(5) and has the value 00000. When I update this value to 00, the problem goes away. When I update the value to 000 or 0000 or 00000, it comes back.

Where would I check the precision for a varchar2 column in Oracle?

You already know the precison - a VARCHAR2(5) has a precision of 5. However, it would be good to know if that is 5 bytes or 5 characters and that depends on the "semantics" used. Toad will show that as VARCHAR2(5) BYTES but you can always check with your DBA.

Sounds like you have a characterset issue where those 5 characters won't fit in 5 bytes but if the issue only shows up in the Oracle Connector, I'd be contacting your official support provider and see if perhaps there is a know issue that a patch addresses.

In TOAD, both sets of tables are defined as bytes. What other semantics can I ask the DBA about?

That's really all you needed to confirm, that the field is set to hold 5 bytes. The other is CHAR semantics. Lots of articles out there in the wild on the difference, for example here or you should be able to have a chat with your DBA on the subject.

So we still don't know the root cause then?

Know? No. Suspect?

chulett wrote:Sounds like you have a characterset issue where those 5 characters won't fit in 5 bytes but if the issue only shows up in the Oracle Connector, I'd be contacting your official support provider and see if perhaps there is a known issue that a patch addresses.

FYI:

I experienced this error too and found that the Oracle Connector throws this error if it can't match up a column to the actual data.

For the same problem, the Oracle Enterprise stage, however, throws a much more meaningful error that says "Could not find input field 'mycolumnname'".

i.e. I had a misspelling in my column name as defined on the columns tab of the connector.

It also occurs if you preform a database function against the column in the SELECT clause and don't explicitly specify "AS mycolumnname".

Hope this helps someone.