Oracle stage extracting Char strange behavior

NSIT-DVP-01 · Post by **NSIT-DVP-01** » Thu Oct 25, 2012 2:25 am

Hello everyone,

I have a strange bug: a server job makes the following query in the Oracle stage:

SELECT ' ' AS Column1 FROM myTable.

Column1 originally does not exist in myTable, and is defined as a Char(10) in the Oracle stage.

As a result: the extracted value is : ' ' or '<blank><blank><blank>' instead of just ' ' or '<blank>'.

Another test: if the query is: SELECT 'test' AS Column1 FROM myTable -
the value is trimmed to 'tes'!

If I augment to Char(20) - it extracts 'test<blank><blank>'

When changing the column definition in the stage to VarChar - the issue disappears.

I have not found any topic that applies, can anyone please provide any idea of where does it come from?

the APT_ORACLE_PRESERVE_BLANKS is set to False, I have also tried True, but I believe it is not where the problem comes from.

The version of Datastage is 8.7 and the NLS is enabled, if this could help.

ArndW · Post by **ArndW** » Thu Oct 25, 2012 2:56 am

There is a bit of a problem here. A column of Char(10) will never contain just one or two or three blanks - it has a fixed length of 10, no more and no less. Are you certain that you are discussing Char(10) or perhaps VarChar(10)?
What happens if you change your select to

Code: Select all

SELECT CAST('' AS Char(10)) AS Column1 FROM myTable

NSIT-DVP-01 · Post by **NSIT-DVP-01** » Thu Oct 25, 2012 3:30 am

Thank you for the fast reply. I agree that it should have 10 characters.

Actually, this job has just been migrated from 7.5, where it had normal behavior - 10 caracters for a char(10). So when making a

Code: Select all

 SELECT '<blank>' AS Column1 FROM myTable

we were obtaining 10 blanks.

I am 100% sure I use a Char(10). Additionally, if I change it to a Varchar(10) - everything becomes normal - I get exactly the value I select.

So, trying your suggestion: same result as before:

Code: Select all

SELECT CAST('' AS Char(10)) AS Column1 FROM myTable

gives '' (empty string) when viewing data,

Code: Select all

SELECT CAST('<blank>' AS Char(10)) AS Column1 FROM myTable

gives '<blank><blank><blank>' and

Code: Select all

SELECT CAST('test' AS Char(10)) AS Column1 FROM myTable

gives 'tes'

I have checked the executed query to make sure it is the one I specified and it is. Otherwise, I have a 'Using NLS map ISO8859-1' if this has any importance.

ArndW · Post by **ArndW** » Thu Oct 25, 2012 4:02 am

Is the Oracle instance a different one as well? I wonder if your tables/databases string types are declared as BYTE or CHAR based and if that might have an effect.

I agree that what you are seeing is wrong, i.e. returning something different than what is expected.

NSIT-DVP-01 · Post by **NSIT-DVP-01** » Thu Oct 25, 2012 4:18 am

So, I am trying to check it for the BYTE/CHAR, I found a topic that makes me suspect something like that as well:

viewtopic.php?t=125403&highlight=oracle+stage+char

it says:

With NLS enabled Oracle reports that it uses three bytes per character (and, indeed, mostly does).

DataStage's NLS map translates that into DataStage's internal UTF mapping (variable number of bytes per character) when reading, and back again when writing.

NSIT-DVP-01 · Post by **NSIT-DVP-01** » Thu Oct 25, 2012 4:26 am

and both 7.5 an 8.7 use Oracle 10g.

NSIT-DVP-01 · Post by **NSIT-DVP-01** » Thu Oct 25, 2012 4:40 am

So the NLS_LENGTH_SEMANTICS is BYTE on both sides - 7.5 and 8.7

ArndW · Post by **ArndW** » Thu Oct 25, 2012 7:12 am

Can you see if changing it to CHAR makes a difference (You don't need to change the whole database, just your one table).

NSIT-DVP-01 · Post by **NSIT-DVP-01** » Thu Oct 25, 2012 8:25 am

I am going to try it! as soon as I find how to do it for just one table

NSIT-DVP-01 · Post by **NSIT-DVP-01** » Fri Oct 26, 2012 2:31 am

Finally, I don't have enough privilege to alter the NLS_LENGTH_SEMANTICS

Otherwise, I was thinking about the explanation of what is going on:

- If due to NLS each character uses 3 bytes, then a CHAR(10 bytes) will store exactly 3 characters.

- So depending of the length of my string I am trying to put in it, it will either add blanks to extend to 10 bytes or to cut if it is bigger than 10 bytes.

-We didn't face this problem in 7.5 since no NLS was enabled.

Does it make sens?

ArndW · Post by **ArndW** » Fri Oct 26, 2012 2:58 am

Yes, that would be a good assumption and the likely cause.

NSIT-DVP-01 · Post by **NSIT-DVP-01** » Fri Oct 26, 2012 3:00 am

thank you very much for your help!