Rows are not inserting in Oracle Table

DSRajesh · Post by **DSRajesh** » Wed Feb 02, 2011 8:28 pm

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

ppgoml · Post by **ppgoml** » Wed Feb 02, 2011 9:46 pm

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.

DSRajesh · Post by **DSRajesh** » Wed Feb 02, 2011 10:24 pm

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.

DSRajesh · Post by **DSRajesh** » Thu Feb 03, 2011 12:38 am

can any please help about this error

ray.wurlod · Post by **ray.wurlod** » Thu Feb 03, 2011 12:38 am

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.

ppgoml · Post by **ppgoml** » Thu Feb 03, 2011 12:55 am

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.

DSRajesh · Post by **DSRajesh** » Thu Feb 03, 2011 1:11 am

ray,

there are the previleges for the user to insert records.

but still it is failing to insert records.

Shruthi · Post by **Shruthi** » Thu Feb 03, 2011 1:42 am

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

ray.wurlod · Post by **ray.wurlod** » Thu Feb 03, 2011 2:48 am

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.

jwiles · Post by **jwiles** » Thu Feb 03, 2011 3:22 am

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.

Code: Select all

transformer------>Oracle- - - - - ->Peek------->SeqFile

"- - - - - -> is a reject link"

As stated, Oracle will add a new column--sqlcode--to each rejected record, which can be used to determine the reason for rejection.

Regards,