Oracle Enterpirse Stage - Records rejected without warning

[tanay.sd]

Hi,
I have a job where i am inserting records in an oracle enterprise stage, the problem is that records do not get inserted in the table. Job finishes successfully, in the log, i can see an entry that tells me n number of records were rejected.

Metadata in the job design and target table match. I can insert records into the table outside Datastage via oracle interface.

Can anyone suggest what can be the possible reason for record rejection?

p.s. sorry for a generic title of the post, could not think of anything better.

[ray.wurlod]

Does the executing user have INSERT privilege to the table in question?

(If you don't tell us, we have to ask.)

[tanay.sd]

Yes it has requisite privileges, I am sorry, i forgot to mention it, with the same user i could insert records via oracle interface.

[chulett]

I'm assuming the 'n' that were rejected were a subset of the 'x' you tried to load, yes? Have you captured/identified the rejects? Seen any common issues with them?

[tanay.sd]

I'm assuming the 'n' that were rejected were a subset of the 'x' you tried to load, yes? Have you captured/identified the rejects? Seen any common issues with them? ...

No, all records were rejected.

[chulett]

OK, start back at the beginning. What exactly are you doing in the OE stage: settings, action, etc etc? Any non Varchar fields involved?

[tanay.sd]

Oracle stage does an upsert with user defined update and insert, apart from varchar, there are three other data types, timestamp, decimal, char.

I captured the rejects using a peek stage and then using these values, successfully inserted a row via oracle interface. The values for all the columns are as expected. Most of the fields with datatype as timestamp are populated by using datastage function currentTimestamp and one field with datatype as timestamp which is derived from a field from another table, i am using to_date function to convert it into the desired format.

and also I suspected a particular column to play truant (datatype - decimal) - i am using a cast function to explicitly match the correspoding column definition in target table.

Note - Column definitions were imported using orchestrate schema definitions.

[chulett]

Please post examples of the actual 'formatted' data you are trying to load to the Timestamp and Decimal fields, plus your user-defined sql. And you are targetting DATE or TIMESTAMP fields in the table with your Timestamp data?

[tanay.sd]

this is the insert sql as it occurs in the oracle stage,

INSERT
INTO schema.table
(
ACDNT_ID ,
ACDNT_STAT_ID ,
SUB_ID_CD ,
MBR_SFX_CD ,
ACDNT_DT ,
ACDNT_TRMNTN_DT ,
APLCTN_CREAT_USER_ID_CD,
APLCTN_UPDT_USER_ID_CD ,
REC_CREAT_DT ,
REC_CREAT_USER_ID_CD ,
REC_UPDT_DT ,
REC_UPDT_USER_ID_CD ,
RPLY_BY_DT ,
ACTV_IND ,
SEND_UPDT_IND ,
UPDT_SENT_DT ,
INCDNT_RPT_SEND_IND ,
INCDNT_RPT_SENT_DT ,
INCDNT_RPT_DOC_NUM_TXT ,
WRNG_MSG_SEND_IND ,
WRNG_MSG_SENT_DT ,
LOB_DETL_ID ,
DIAG_CD_CHGD_IND ,
EMP_IND ,
PURSUIT_STAT_ID ,
SBRGTN_RULE_ID
)
VALUES
(
9999999996 ,
5 ,
'qwerty' ,
'qwer' ,
systimestamp ,
to_date(sysdate, 'YYYY-MM-DD HH24:MI:SS'),
'qwerty' ,
'qwerty' ,
systimestamp ,
'qwerty' ,
systimestamp ,
'qwerty' ,
systimestamp ,
'Y' ,
'Y' ,
systimestamp ,
'Y' ,
systimestamp ,
'qwerty' ,
'Y' ,
systimestamp ,
'OR' ,
'Y' ,
'Y' ,
3 ,
000000051986337.
);
and using this query i can insert records via oracle interface.

Yes, date fields in target table are being populated using timestamp data generated by datastage function.

[dsedi]

As Craig said,
if you have captured the rejects thru reject link, write that into a dataset or SEQ file..what is the SQLCODE for the rejects?

[tanay.sd]

SQLCODE for rejects is -2291, which is an error code for master-child relationship - another team is investigating the data, will keep everyone posted.

Thanks for replies.

[vmcburney]

Oracle reject handling is better in version 8.1 of DataStage where you can migrate to the Oracle Connector instead of Oracle Enterprise. More flexible and robust handling of different types of Oracle rejects.