Page 1 of 1
link.DBMSCODE
Posted: Mon Mar 26, 2007 2:34 pm
by 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
Posted: Mon Mar 26, 2007 3:35 pm
by chulett
Uncheck the 'Reject Row' box in the constraint. And make sure your OCI array size is 1.
Posted: Mon Mar 26, 2007 3:59 pm
by 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
Posted: Mon Mar 26, 2007 4:31 pm
by 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.
Posted: Mon Mar 26, 2007 5:15 pm
by chulett
sheema wrote: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:
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'.
Posted: Mon Mar 26, 2007 5:37 pm
by 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.
Posted: Mon Mar 26, 2007 8:36 pm
by 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.
Posted: Mon Mar 26, 2007 8:57 pm
by 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.
Target Table Rejection
Posted: Mon Mar 26, 2007 10:09 pm
by rachitha
Sheema,
Give Constraint as <outputlink.rejected> for Rejected Rows link.
And make sure in source you have given filter condition.
Thanks,
Rachitha.
Posted: Wed Mar 28, 2007 1:24 pm
by 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.
Posted: Wed Mar 28, 2007 3:03 pm
by 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.