ODBC stage loading Oracle funny characters

SBSA_DW2 · Post by **SBSA_DW2** » Fri Nov 27, 2009 5:42 am

Hi Guys

Today a colleague asked me to look into a job for her that

(1) takes a flat file and loads using ODBC stage into an oracle database. This works fine, 100%, no errors, no warnings just the green green icons of success. The ODBC stage uses a defined DSN in the .odbc.ini file. 2 columns in the table, column1 is double(15) and column2 is varchar(50)

the data is in the table...

(2) Job 2 uses an ODBC stage reading from the table loaded in step (1). The double column comes out fine, but the varchar is filled with crazy characters.. characters you wouldn't trust with your sister.. shady.. real shady (unreadable cahracters).

When running the job to extract from the table the error: "APT_CombinedOperatorController,0: Invalid character conversion found converting to ISO-8859-1, substituting" appears.

I have searched the forum for the error and there is reference to NLS and code pages switching, but we don't have NLS enabled.

The kicker is we can load to the table but can't read from the table using the same DSN from .odbc.ini.

I'm going to look at including the oracle drivers installed in the LD_LIBRARY_PATH so that we can use the oracle plug-in stage over the odbc stage.

any suggestions welcome

chulett · Post by **chulett** » Fri Nov 27, 2009 8:57 am

What is the character set of both the source file and the target database? If you use another tool, say like Toad, to view the table are the values in the string fields still 'corrupted'?

SBSA_DW2 · Post by **SBSA_DW2** » Mon Nov 30, 2009 3:02 am

thanks Craig
I'm not sure on the character set but the data is viewed fine via TOAD.

the plot thickens..

We have now created a Server version of the job on the same server, used the same ODBC settings and the data reads fine. This is a workable workaround but now the question.. what is the difference between the two stages and why would there be an issue..

thanks!

chulett · Post by **chulett** » Mon Nov 30, 2009 8:04 am

It's going to come down to character sets, so to take this any further you'd need to know what that is in your Oracle database and your PX job before anyone could help.

SBSA_DW2 · Post by **SBSA_DW2** » Thu Dec 17, 2009 11:57 pm

RESOLVED - turned out the dsenv and lib links weren't set up properly.