sqlcode is: -2290

gssr · Post by **gssr** » Wed Dec 30, 2009 10:36 am

I am updating and inserting the table with the following job

Dataset --> Transformer --> Oracle -->Reject

While executing it throws me the following errorm

Code: Select all

Orac_load,1: Open failed for insert:
insert is: (<Insert Query>
)
sqlcode is: -2290
esql complaint: ORA-02290: check constraint (Column Constraint) violated

I searched dsx, and found
viewtopic.php?t=127128&highlight=sqlcode+is%3A+-2290

Its not helping ,so need more help....

nikhilanshuman · Post by **nikhilanshuman** » Wed Dec 30, 2009 11:17 am

A simple solution is to add constraint in transformer stage on the incoming data to verify if it is satisfying the check constraint which is defined in the table.If it is satisfying the constraint,then only pass data to target OCI stage.

For example : Suppose there is a table "Emp".It contains a column "Salary".There is a check constraint which says the salary must be greater than 2000(i.e. salary>2000).in this case,in the transformer you will have to add a constraint salary>2000.In this way,OCI stage will get only those valid records which satisfy the check constraint.

Rejecting records which violate the check constraints :

=> From transformer take a link for "otherwise".This link will contain all the data which has violated the check constraint.
=>add a funnel to capture the invalid data from tranformer as well as the OCI reject link.
=>In this way the data can be captured as well.

gssr · Post by **gssr** » Wed Dec 30, 2009 11:34 am

Thanks for your thoughts Nikhil,

Actually we got more than 20 constraints in the table

Its not possible to get all that in to the transformer...

Actually, i dont know why this error is arising??
Can you give me the root cause about this...

Thyanks in advance

chulett · Post by **chulett** » Wed Dec 30, 2009 11:40 am

How is that other post not helping? In short you need to understand why you are violating that check constraint (look it up in the database or talk to your DBA if you are unsure) and then ensure in your job that you pass only "good" values to that column. That could mean setting "bad" ones to a default value or not sending the record to the database at all... depends on your requirements.

Doesn't matter how many there are, check them all if you're unsure which one you're violating. And just specifically look at check constraints rather than constraints in general on the table. Most of the time they will be of the simple "is not null" variety but could also be enforcing a list of valid values (etc).

gssr · Post by **gssr** » Wed Dec 30, 2009 11:48 am

I can understand that by using Nikhil solution ,i can get the same output.

But, i want to know which is the cause for this ERROR!!

Any idea

I am currently trying to implement Nikhil's design, and post the update asap.

chulett · Post by **chulett** » Wed Dec 30, 2009 12:03 pm

gssr wrote:But, i want to know which is the cause for this ERROR!!

Any idea

You violated a check constraint, nothing more and nothing less. Talk to your DBA if that is still unclear for some reason. You need to know what check the constraint is enforcing before you'll have a clue what the actual "cause" is.

gssr · Post by **gssr** » Wed Dec 30, 2009 12:12 pm

i checked with the constraint, and it is

Code: Select all

Ord_ind in (1,2,3,4,5,6)

The insert records are all having 6 as Ord_ind

But Still it was throwinmg the same error..

chulett · Post by **chulett** » Wed Dec 30, 2009 12:29 pm

Then perhaps there's another constraint or you aren't really sending the "6" that you think you are. Any chance it's something silly like a "6 " coming in?

gssr · Post by **gssr** » Wed Dec 30, 2009 12:56 pm

I am just passing 3 record only , I can view the records in the input dataset as ORD_IND is all 6

In the error message, i am getting only this constraint as Violated!!

nikhilanshuman · Post by **nikhilanshuman** » Wed Dec 30, 2009 1:06 pm

gssr wrote:I am just passing 3 record only , I can view the records in the input dataset as ORD_IND is all 6

In the error message, i am getting only this constraint as Violated!!

put a peek stage after transformer and see what is actually going to target.The column value will be visible in the job log.

Check if the column mapping is proper.

Try hard coding the value in the transformer to 1 or 2.....

gssr · Post by **gssr** » Wed Dec 30, 2009 1:25 pm

I tried to get into another dataset in target, and i can able to view it in the target output!!
I will try to use a peek stage and update about the feedback

gssr · Post by **gssr** » Wed Dec 30, 2009 1:37 pm

I checked the DDL of the table with constraint

Code: Select all

ALTER TABLE Test.Table1
ADD CONSTRAINT CHK_ORD_IND CHECK
(ORD_IND IN(0,1,2,3,4,5,6,7,8))
 ENABLE
;

Is that correct to mention

(ORD_IND IN(0,1,2,3,4,5,6,7,8)) INSTEAD OF (ORD_IND IN (0,1,2,3,4,5,6,7,8))

Anything this may cause!!!

gssr · Post by **gssr** » Wed Dec 30, 2009 1:39 pm

gssr wrote:

(ORD_IND IN(0,1,2,3,4,5,6,7,8)) INSTEAD OF (ORD_IND IN (0,1,2,3,4,5,6,7,8))
Anything this may cause!!!

in the above is actually '8' and ')'

chulett · Post by **chulett** » Wed Dec 30, 2009 1:40 pm

Is the only difference the space after the "IN" verb? No, that makes no difference, both are equally valid.

We know. If you enable the "Disable Smilies" option in that post, that would fix that.

gssr · Post by **gssr** » Wed Dec 30, 2009 4:19 pm

I dropped the Constraint in the table, the job works fine now,

Still could not able to get the error Source !!!

Thanks for your Thoughts!!!!