Strange Characters

[#1]

We are having issues on the BODI extracts having strange characters. We get data from the source system (SAP R/3 table) and put all the data in the SAP download file. During this process, all the unique characters from S1P were consistently and successfully placed in the file. The SAP download file will be used to load an staging Oracle table. The problem now occurs here as the unique characters were not transferred as it is. The BODI version we used is 11.7.3.

BODI Application Server:

- NLS_LANG: AMERICAN_AMERICA.WE8ISO8859P15

-Code Page: 1252

ORACLE Database

- NLS LANG: AMERICAN_AMERICA.UTF8

- Version: 8.1.7

[RyanChamplin]

Is the column in the Oracle database you're loading this data into defined as a varchar2 or nvarchar2?

There are two different character encoding settings in Oracle that control which encoding is used for varchar2 vs. nvarchar2.

I'm assuming this was obvious but thought I would double check. The DBA can check the NLS_CHARACTERSET vs. the NLS_NCHAR_CHARACTERSET which should align with the column type you're using.

Ryan

[werner_daehn]

You should set the NLS_LANG of the DI server to UTF-8 as well, change the datastore to utf-8 and make sure the engine runs in UTF-8 mode as well. Pre 12.0 you had to set that in the \DI\bin\DSConfig.txt file, with 12.0 onwards this happens automatically.

https://wiki.sdn.sap.com:443/wiki/display/BOBJ/Multiple+Codepages

[Former Member]

Many thanks for the response.

the problem lies on staging the data on the target database. The file FTPed to BODI appliciation server is in order.

The problem lies on staging the data on the target database with NLS_LANG=AMERICAN_AMERICA.UTF8 as confirmed by the database administrator.

I have alter the database driver in Oracle for Windows from AMERICAN_AMERICA.WE8ISO8859P15 to AMERICAN_AMERICA.UTF8 using regedit.

The DSconfig.txt has this also correct format: AL_Engine= is completed successfully.

[Former Member]

Hi Carlos,

Are your varchars defined as byte i.e. VARCHAR2(20 BYTE). A multi-byte string with 20 characters will not fit into a column defined as 20 BYTE. For columns containing multibyte characters you need to define them as VARCHAR2(20 CHAR).

Regards

Dan

Edited by: Dan Hough on Jan 27, 2009 9:27 AM

[Former Member]

Hi,

I've made adjustment on the datatype and job finished. However, same scenario appeared. Incorrect character still being staged on the database. See sample below.

Result:

abk Abkhaz "¿¿¿¿¿ ¿¿¿¿¿¿ (a¿sua byz¿¿a); ¿¿¿¿¿¿ (a¿s¿¿a)"

abq Abazin / Abaza ¿¿¿¿¿ ¿¿¿¿¿¿ (abaza b¿z¿¿a)

bug Buginese ¿¿ ¿¿¿¿ (basa ugi)

bul Bulgarian ¿¿¿¿¿¿¿¿¿ ¿¿¿¿ (b¿lgarski ezik)

It should be:

abk Abkhaz аҧсуа бызшәа (aṗsua byzu0161ʷa); аҧсшәа (aṗsu0161ʷa)

abq Abazin / Abaza абаза бызшва (abaza bəzu0161ʷa)

bug Buginese ᨅᨔ ᨕᨘᨁᨗ (basa ugi)

bul Bulgarian български език (bŭlgarski ezik)

The target datastore (database) has been set to utf-8. The database oracle driver from the registry has been set to NLS_LANG=AMERICAN_AMERICA.UTF8. The source file is in UTF8 as well.

The trace log seems to be ok now. See below.

(11.7) 01-28-09 06:53:49 (7696:10132) JOB: The initial environment locale

But no improvement on the characters. WHat else goes wrong? Is this a bug version? Is this something with the client language?

Thanks.

[Former Member]

I've seen this problem when the Oracle home wasn't configured correctly, so the Oracle client was ignoring the some registry settings. The work around (until we fixed the Oracle home) was to create an environment variable called NLS_LANG. For example NLS_LANG=AMERICAN_AMERICA.UTF8.

[Former Member]

Hi,

Thanks for the feedback. How to set this environment variable? How this workaround affect the job loading on target database?

Many thanks for the help.

[Former Member]

The NLS_LANG setting in your Oracle client should match the codepage of the interface using the Oracle client. So if BODI is using UTF-8, then the Oracle client should be set to UTF-8.

If you set an environment variable called NLS_LANG, the oracle client will use this setting.

So on your BODI server you need to set the environment variable:

Right click on your 'My computer' icon and select properties.

Go to the advanced tab and select 'Environment Variables'

Create a new variable called NLS_LANG and set it's value to AMERICAN_AMERICA.UTF8

Be careful with setting the Oracle client to UTF8, all the other apps on the server using Oracle client will need to talk in UTF8.

[werner_daehn]

or regedit -> Local Machine -> Software -> Oracle -> .... NLS_LANG

Yes, that question mark is the Oracle SQL*Net character replacement, it will be fine once youchanged the NLS_LANG.

[Former Member]

Hi Dan,

No postive result. Still characters went inverted question mark

CODE ENGLISH NAME ORIGINAL FORM

abq Abazin / Abaza ¿¿¿¿¿ ¿¿¿¿¿¿ (abaza b¿z¿¿a)

abk Abkhaz "¿¿¿¿¿ ¿¿¿¿¿¿ (a¿sua byz¿¿a); ¿¿¿¿¿¿ (a¿s¿¿a)"

ady Adyghe ¿¿¿¿¿¿¿¿ (ad¿g¿bz¿) / ¿¿¿¿¿¿¿¿ (ad¿gabz¿)

bua Buryat ¿¿¿¿¿¿ ¿¿¿¿¿ (burjaad ¿älän)

bug Buginese ¿¿ ¿¿¿¿ (basa ugi)

bul Bulgarian ¿¿¿¿¿¿¿¿¿ ¿¿¿¿ (b¿lgarski ezik)

Oracle client is already configured with NLS_LANG=AMERICAN_AMERICA.UTF8.

BODI target datastore set to utf-8 codepage.

Database driver set to NLS_LANG=AMERICAN_AMERICA.UTF8.

Created environment variables called NLS_LANG with AMERICAN_AMERICA.UTF8.

DSconfig.txt is set to .utf-8.

All setup has been already synchronize but still no luck.

However I checked the Regional and Language Options in the control panel and set to the following:

Standards and formats : English (United Kingdom)

Location : United Kingdom

Langauage fo non-Unicode programs : English (United Kingdom)

Codepage conversion tables: : All check boxes are ticked.

Is this something worthwhile to check?

Is this something we can call a BODI version?

Thanks.

[Former Member]

Is there any difference in the result when running with bulk load on or off?

[Former Member]

Hi,

No difference on both. According to database team, The orcale client is installed on UNix server, I gave them the source input file and they successfully inserted the same character using SQLplus. However, our BODI jo don't use this tool.

I think i'ts expected with them to inserted the same character because they run it on their own server. But the scenario is, the data are being staged from different server and here the conversion went strange.

Is there any resolution fo this?

[Former Member]

This should work. Can you raise a case with support? They might be able to replicate your situation.

[werner_daehn]

Stop the jobserver and exit svrmgr tool.

In the shell type: set NLS_LANG=AMERICAN_AMERICA.UTF-8; export NLS_LANG

start the jobserver so its Oracle client picks up above environment variable.

Go to all Oracle datastores in your repo and set the datastore codepage to utf-8.

Any difference now? btw, how do you validate what the target table looks like? Please use the Oracle provided tool "SQL Developer" for that. Windows tools like SQL*Plus might not show the characters correctly. I don't think that's your issue but anyway.