ORAOCI 9i stage - problem with decimal column

dwuser · Post by **dwuser** » Fri Apr 13, 2007 7:08 pm

We are extracting the source data from Oracle 9i database using ORAOCI91i stage . The stage has two decimal columns and a date column.

But sometimes the decimal column have some text appended to it. this is not occuring everytime we trigger the job.

Like :

999999999 ojects/edw_prod_7
instead of 999999999

kcbland · Post by **kcbland** » Fri Apr 13, 2007 7:22 pm

Did you change any settings on the array size or transaction options on the stage?

dwuser · Post by **dwuser** » Fri Apr 13, 2007 8:20 pm

No, we haven't changed any settings in the job.
But we have changed the T30FILE value from 200 to 500

To give a backgroud on this issue:

We have recently upgraded from 7.0 to 7.5.2.
We had some problem with T30FILE size so we changed it from 200 to 300.

The three column are loaded by the following SQL
SELECT TRIM(RCT.TRX_NUMBER), '999999999', TO_CHAR(RC.LAST_UPDATE_DATE, 'YYYY-MM-DD HH24:MI:SS')
FROM APPS.RA_CUST_TRX_LINE_GL_DIST_ALL RC, APPS.RA_CUSTOMER_TRX_ALL RCT

Metadata for the 3 columns in the source ORAOCI9i stage.
Decimal(38),Decimal(38) and Timestamp(38)

ray.wurlod · Post by **ray.wurlod** » Sat Apr 14, 2007 1:50 am

T30FILE setting is irrelevant; it relates only to hashed files.

What are the data types of RCT.TRX_NUMBER and RC.LAST_UPDATE_DATE in the Oracle table, as reported by DESCRIBE ?

Is this really a Cartesian product, or have you not posted the WHERE clause that joins the two tables?

chulett · Post by **chulett** » Sat Apr 14, 2007 6:19 am

We had a similar experience with the OCI stage in 7.5.2 - junk after certain column values. For us it wasn't timestamp or decimal fields, but just varchars substringed from a CLOB. And it only happened when using a 10g client to access our 10g instance.

For us, reverting back to a 9i client solved the problem. Not sure how helpful that is except to note that others on 7.5.2 has seen something similar so that might be helpful when you contact Support - which is what I'd suggest you do.

There's no setting that should be able to cause that as far as I know, seems to me to be some sort of issue with 7.5.2.

For the record, what is your server platform's O/S? And what is the exact version of your 9i client? Some are vastly more buggy than others, you may need to upgrade your Oracle software.