NLS Error in DSExecute routine

LD · Post by LD » Tue Sep 13, 2011 3:53 am

Hi,

I'm using DSExecute routine in a batch job to run some sql in oracle DB. DSExecute calls a unix shell script which in turn connects to DB and execute a sql file with multiple sql statements.

Job is running fine in old projects. We created a new project recently but the routine call is failing in new project with below error as the cmd_output from the routine.

"ERROR:ORA-12705: invalid or unknown NLS parameter value specified"

Please let me know if I need to make some changes in new project as this was first execution in this project and is failing with said error.

Thanks,
Shashank

chulett · Post by **chulett** » Tue Sep 13, 2011 6:29 am

ORA-12705 errors generally mean an invalid value in the NLS_LANG environment variable. Not sure why that would only happen in one project, but you first need to see if that is the case.

LD · Post by LD » Tue Sep 13, 2011 7:23 am

Hi Craig,

Thanks for helping me out. I checked the NLS_LANG in both DS job log and DB and it is some i.e. NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1.
And as you pointed, this is happening in other project too, not just the new one.
Another thing is, OCI stage is running fine in both the projects. When I'm using DSExecute to connect using sqlplus, that is failing in both the projects. Also in old project, bulk loader in PX job is also failing with same error.

Not sure but looks like when we use sqlplus through Datastage, it is failing. everything else is running. sqlplus from unix command prompt is running fine and OCI stage is running fine.

DSExecute fails and bulk load fails. Please let me know if any thing else I can check here.

Thanks.
Shashank

chulett · Post by **chulett** » Tue Sep 13, 2011 8:25 am

Hard to say. First guess when Oracle things are fine from the command line but not from DataStage would be the environments each is leveraging. Perhaps your command line sessions are using different values for $ORA_HOME and your "shared library path" then they do in job? That's the first place I would look, compare the Oracle environment the jobs are using versus the command line.