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
ORAOCI 9i stage - problem with decimal column
Moderators: chulett, rschirm, roy
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)
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)
Bharathi
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.![Confused :?](./images/smilies/icon_confused.gif)
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.
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.
![Confused :?](./images/smilies/icon_confused.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers