Unable to read or update decimal values

[jgajardo]

Hello everyone,

I need your help

Configuration:
We have a brand new installation of PX v8.1 on a Windows Server.
-The Database is ORACLE 11g.
-Two (2) Oracle clients are installed on the server machine is 9.2.0 and 11.1.0.
-Currently using the 9.2.0 Oracle client on the server


Problem:
We cannot SELECT, UPDATE or INSERT a field of ORACLE format NUMBER (10,2)
that has a value with numbers passed the "." ie: "500" works, "25.25" doesn't work.

If all records in the column have values with numbers that don't have a decimal part it works. ie: 500

If one of the records in the column has a value with a decimal value part (ie: 25.25), it fails with the following error:

Code: Select all

APT_Decimal::assignFromString: invalid format for the source string.

When I try to view the data I get this:

Code: Select all

>##E IIS-DSEE-TFOR-00001 11:51:53(000) <Oracle_Enterprise_63,0> Failure during execution of operator logic.
 ##I IIS-DSEE-TFOR-00094 11:51:53(001) <Oracle_Enterprise_63,0> Output 0 produced 0 records.
>##F IIS-DSEE-TFDE-00030 11:51:53(002) <Oracle_Enterprise_63,0> Fatal Error: APT_Decimal::assignFromString: invalid format for the source string.
>##E IIS-DSEE-TFPM-00192 11:51:53(000) <node_node1> Player 1 terminated unexpectedly.
>##E IIS-DSEE-TFPM-00338 11:51:53(000) <main_program> APT_PMsectionLeader(1, node1), player 1 - Unexpected exit status 1.
>##E IIS-DSEE-TFSC-00011 11:51:58(000) <main_program> Step execution finished with status = FAILED.
 ##I IIS-DSEE-TCOS-00026 11:51:58(001) <main_program> Startup time, 0:06; production run time, 0:00.

What I know and have tried so far
I've imported the table definition using "Orchestrate schema definitions"
as specified here: viewtopic.php?t=121146

The explanation of the error from "Parallel Engine Message Reference" is :

Code: Select all

DSEE-TFDE-00030 APT_Decimal::assignFromString: invalid format for the source string. 

Explanation: An error occurred when converting a string to a decimal number. 

The string must consist of: 

- optional leading white space (blanks or tabs) 
- an optional + or - sign (if absent, the number is assumed to be positive) 
- 0 or more decimal digits (0-9) to the left of the decimal separator 
- an optional decimal separator (locale dependent) 
- 0 or more decimal digits to the right of the decimal separator 
- optional trailing white space 
 
System action: None 
User response: Correct the invalid string, 
               or (if this is occurring during an import operation) 
               specify the desired error handling for invalid strings.

I made sure we're using the same NLS in the Oracle Enterprise stage that is used in the DataBase (ISO-8859-15)

I can SELECT, UPDATE and INSERT this values using TOAD on my machine
I can INSERT these values into the table using LOAD in the Oracle Enterprise stage
Any ideas?

NOTE: I've narrowed down the problem to this column format,
as the original message was a lot less explicit. The job I'm testing is a simple SELECT into a file

[ArndW]

Are either your database or DataStage using a locale which might use ',' as the decimal delimiter?

[jgajardo]

Are either your database or DataStage using a locale which might use ',' as the decimal delimiter?

We thought of that... but after verification and changes done in order to eliminate any doubts... we have them both with the same NLS.

One more thing, the regional settings on the windows server indicate '.' as the decimal delimiter (the windows install on the server is in french).

the wierdest part of all this is that the LOAD works flawlessly

[jgajardo]

We found a solution !

I'd like to thank the academy , a special thanks to Norman Simpson for finding the solution,
Jean Porter for inspiring it and ArndW for caring !

We added the following to the user defined environment variables for the project:

Code: Select all

NLS_NUMERIC_CHARACTERS=.,

it did the trick!

Question: Is this THE place to do the change or can we set this up somewhere else better?

[ray.wurlod]

In the Administrator is probably the best place, because the setting is then global for the entire project. (But must be set in each project separately.) You could consider setting the environment variable in every DataStage user process's profile, perhaps via their logon script.

If necessary you can override or unset the environment variable in particular jobs by including the variable as a job parameter.