link.DBMSCODE

[sheema]

I have a simple job,where i have a source file with 2000 records
and a target oracle stage.I have a constraint
link1.col='XYZ' than i load the data into Oracle table.
so in the source there are 20 records which match this constraint.
10 records in the source file are duplicates,so i am getting the
warning message

"ORA-00001: unique constraint violated".
DBMS.CODE=ORA-00001

so i want to capture these 10 rejected records into a sequential file.
i give the following constraint in the
rejectslink

link1.DBMSCODE ='ORA-00001'.

But i see that all the other records which do not match the
constraint link1.col='XYZ' are also captured into the rejects.

How can i capture just the 10 records into the rejects file.

Thanks
sheema

[chulett]

Uncheck the 'Reject Row' box in the constraint. And make sure your OCI array size is 1.

[sheema]

The Reject Row box is unchecked and the array size is also set to 1.
The reject link is the last link in the link ordering too.
But still i do not get the expected rows into rejects file

[us1aslam1us]

I am not sure but have you tried including the link1.col='XYZ' with the DBMS.CODE constraint? It seems you are rejecting all the rows from the source which possess duplicate values.

[chulett]

The Reject Row box is unchecked and the array size is also set to 1.
The reject link is the last link in the link ordering too.
But still i do not get the expected rows into rejects file

This doesn't make a lot of sense. Without the 'Reject Row' checked, all that will go down the link are any rows that satisfy the constraint. So if what you have in that constraint is:

Code: Select all

link1.DBMSCODE ='ORA-00001'

Nothing other than that can go down the link. So, obviously we're missing a critical piece of information here if you are saying other information is indeed being 'rejected'.

[ray.wurlod]

Your Transformer stage has two outputs; one going to the Oracle table (let's call this LinkA) and one handling rejects (LinkB). LinkA has NO constraint expression. LinkB is marked as handling rejects. Define an integer column on LinkB derived as the output link variable LinkA.DBMSCODE.

If you need other constraints, do this in a preceding Transformer stage. Handle the database rejects separately.

[sheema]

I did not get what ray was explaining.

Define an integer column on LinkB derived as the output link variable LinkA.DBMSCODE.

If you need other constraints, do this in a preceding Transformer stage. Handle the database rejects separately.


can some one explain that for me.

[ray.wurlod]

Code: Select all

---->  Transformer1  ----->  Transformer2  ----->  Oracle
             |                    |         LinkA
             |                    | LinkB
             V                    V
          Rejects1              Rejects2

Transformer1 looks after any business rules, sending its rejects out a rejects link.

Transformer2 looks after any rows rejected by Oracle, by having LinkA with no constraint expression and LinkB handling rejected rows.

On LinkB, as well as any columns from the input link, you can define extra columns, for example DBMS_Code. This should be an integer. Its value is the error code returned from Oracle through the link variable LinkA.DBMSCODE.

[rachitha]

Sheema,

Give Constraint as <outputlink.rejected> for Rejected Rows link.
And make sure in source you have given filter condition.

Thanks,
Rachitha.

[sheema]

Thanks,Ray that solution worked out.
But what if ,i have 1 source and 10 business rules to load into 10 different target tables.Should i have rejects for 10 target tables.
Will your solution work out in this case.

[ray.wurlod]

Yes, but you will need separate columns on the rejects link for the DBMSCODE or LASTERR or SQLSTATE link variables from the ten other output links. Name these columns appropriately, such as Table1.DBMScode.