DSXchange

Dear Ray/Chullet,

Datastage job is running successfully and showing number of rows over the link in oracle db but its not inserting records in the oracle table.

source is Dataset
Intermediate stage is Transformer
Target is Oracle Table

Can you please guide me here to sort out the problem

it seems your records were rejected by oracle enterprise stage. you could put a reject link on oracle enterprise stage, and export the rejected records into a flat file. There would be a sqlcode column(indicating why oracle rejects your row) appended to each record,and that would be useful for you to diagnose your problem.

Hi,

Thanks for quick reply.

here i am loading the rejected records into oracle table.

i have applied condition in transformer and passing the records to reject table.

the reject link is showing the records but they are not inserting into reject table.

can any please help about this error

Can they be written to a text file? If so, I suspect that the user does not have INSERT privilege to this particular Oracle table.

DSRajesh wrote:Hi,

Thanks for quick reply.

here i am loading the rejected records into oracle table.

i have applied condition in transformer and passing the records to reject table.

the reject link is showing the records but they are not inserting into reject table.

oracle enterprise supports the reject link, you reject link should put on oracle enterprise stage rather than transformer. and I suggest you write them to a file first.

In you situation, the most likely reason for record rejection may be that
you inserted rows into a table without INSERT privilege, or
you put a null value to a not null column(you should note that, oracle will treat whitespace string and empty string as null.), or
you had an unique index violation.

ray,

there are the previleges for the user to insert records.

but still it is failing to insert records.

I guess the log would say why the records got rejected. What is the warning shown there?

Did you try my suggestion of using a text file (or even a Peek stage) to eliminate non-Oracle causes of the problem?

Create a reject link from the stage that is writing the rejects, and send that link into a text file.

DSRajesh,

You need to add a reject link to the Oracle stage to capture the records which Oracle is rejecting (not being inserted). Then place a peek stage on the output link so that you can at least see the records being rejected inthe log. Follow the Peek with a Sequential File stage to save these rejected records to a text file for further analysis.
As stated, Oracle will add a new column--sqlcode--to each rejected record, which can be used to determine the reason for rejection.

Regards,