NLS_Language environment variable

[cognosun]

I need to add NLS_Lang oracle environment variable in order to allow non-english language into reports ( from Oracle 10g database).

We are on AIX, using Cognos 8.4.1

Can anyone tell me where i need to add this variable, is it in admin profile file ( in server ) or at session commands section ( at Data source creation location in Cognos Administration --> Configuration--> Data source connection )

[sir_jeroen]

You might try using command blocks in your connection.
e.g.
This can be achieved through the use of command blocks. Steps:
1.Navigate to the Directory section of Cognos Connection
2.Go to the data sources tab
3.Click on the "set Properties" section of the Oracle data source
4.Click on the "Connection" tab
5.Click on the Set option of "Open session commands"
6.You can paste the following into the edit section:
<commandBlock>
<commands>
<sqlCommand>
<sql>ALTER SESSION SET NLS_SORT=DUTCH</sql>
</sqlCommand>
</commands>
</commandBlock>
7.Save this and try running the report.

Source: https://www-304.ibm.com/support/docview.wss?uid=swg21342474

[cognosun]

It seems this solution is for sorting.

The Oracle variable which we need is : NLS_LANG = AMERICAN_AMERICA.UTF8

For my problem Cognos gave a straight answer for windows ( single installation ) :

Right click My Computer --> Properties > Advanced > Envriornment Variables--> System Variables --> Add --> NLS_LANG.

But our's is a distributed installation on AIX.

So my doubt is whether i need to add the above variable either in profile file ( the owner of the profile would be the admin) or is it, at the session commands.

[sir_jeroen]

Yeah.. setting the Oracle variable that's one solution. In this way you set language for the client driver on the server. I think this should also be possible for AIX, but that's an OS that's unfamiliair to me.
I would go for the command blocks, because it gives you more freedom and more control because in this way you're not depending of the server administrators.
The command blocks will also work with Framework Manager. (other wise you will have to set the oracle variable on every machine you have FM running on.)

[cognosun]

As i said...this is our variable : NLS_LANG = AMERICAN_AMERICA.UTF8

This worked for us when we added  the same in profile files, on our AIX server ( where cognos is installed)

Can you tell me whether below is what i need to add in the XML Database command ( in the mentioned path)

<commandBlock>
<commands>
<sqlCommand>
<sql>ALTER SESSION SET NLS_LANG = AMERICAN_AMERICA.UTF8</sql>
</sqlCommand>
</commands>
</commandBlock>

[sir_jeroen]

What happens if you execute the line "ALTER SESSION SET NLS_LANG = AMERICAN_AMERICA.UTF8" in an Oracle query tool, e.g. Toad...
If it works there it will most likely also work in Cognos because with code blocks you can
set options just as you can do with Toad .
see also https://www-304.ibm.com/support/docview.wss?uid=swg21342218

[cognosun]

I have tested by adding this block in Open connection commands and Open session commands sections ( in 2 seperate scenarios)...but NLS setting is not working.

The database User ID which we configured in Congos ( Data source level) has ALTER Session provilege.

Any suggestions please....

[cognosun]

there are some 4-5 special symbols in the oracle database...out of which few are getting displayed onto cognos reports...but not all...

can anyone help like why few are not in sync with the database data rather they are appearing as black spots( in reports)

we have same nls setttings in unix database server and our cognos unix server.