[TD3.1] Oracle and string datatype of Oracle functions

[micsto]

I've got a big problem with the returned string datatype of Oracle functions, e.g. LPAD.

With Oracle 10.2.0.2.0, the returned value of LPAD was treated in TD as a normal String.
With Oracle 10.2.0.4.0, the returned value of LPAD is treated in TD as a Long String.
Therefore all applications using LPAD, RPAD etc. in Select statements doesn't work anymore, because they are fetched into normal Strings.

I know one workaround were to cast the returned value of LPAD to VARCHAR2(254), e.g CAST( LPAD( 'A', 10, 'B' ) AS VARCHAR2(254) ), but then I had to change really a lot of Select statements.

Does anybody know a way to achieve that the oracle functions in 10.2.0.4.0 return the same datatype as in Oracle 10.2.0.2.0?
Or is there any setting or parameter on TD side to achive this?

We're using the native Oracle router.

[Jeff Luther]

Another strange one, Michael! I talked with our router guy and he was as surprised as I was. Couple things to note:

* I looked up LPAD info. and got Oracle 11g info here: http://download.oracle.com/docs/cd/B283 ... s_2017.htm
Note it says returns value is TEXT or NTEXT

* he looked up LPAD info. for 10g and got a different return: VARCHAR2, I think he said.

Thus, that lends credence to your saying that this works for you: CAST( LPAD( 'A', 10, 'B' ) AS VARCHAR2(254) )

* It's an Oracle change somehow. He wonders if perhaps something else for you changed but might not be the 10.2.0.2 --> 10,2,0,4 change??

* what neither of can understand is why Oracle would have made a return value change in a rev. out to the 4th rev. number!?

Bottom line: If you can't find another reason for this change (than Oracle version) then using CAST does seems the best solution for you. And, no, there's no parm. in that old TD v3.1 that would control this. BTW, 'officially' v3.1 doesn't/didn't support Oracle v10 and was never certified to run with it.

[micsto]

FYI: Same behaviour in TD 6.0 SP2.