Foeign Key Violation not caught by REJECTED scenario
Moderators: chulett, rschirm, roy
Foeign Key Violation not caught by REJECTED scenario
When i try to insert a record, i want to capture the rejected records, surprisingly 'FOREIGN KEY VIOLATION' is not being rejected, but its rejecting records with invalid lengths. i tried using DBMSCODE ,SQLSTATE, none of them have any value in it.. anyhelp is appreciated
How exactly? Post your constraint derivation here directly from the job, please don't hand type it. Or is that it?
Specifically...
Is your database link called VALID_LINK and what you posted is the constraint from a separate 'reject' link you setup? Also let us know what database and stage you are using - the more details the better, ya know.
![Confused :?](./images/smilies/icon_confused.gif)
Is your database link called VALID_LINK and what you posted is the constraint from a separate 'reject' link you setup? Also let us know what database and stage you are using - the more details the better, ya know.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
While it should work fine, I'm not a big fan of the DRS stage so I don't use it and can't say what kind of an influence it might have on your rejection handling. Unless you've got a wonderful reason for using it, I'd really suggest sticking the OCI stages for Oracle.
I prefer REJECTEDCODE, so if that was literally your reject link constraint you could try changing it to:
And see if that changes the behaviour. For the better. ![Wink :wink:](./images/smilies/icon_wink.gif)
I prefer REJECTEDCODE, so if that was literally your reject link constraint you could try changing it to:
Code: Select all
VALID_LINK.REJECTEDCODE
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
The issues aren't with the OCI stage, it's with multiple actions in user-defined sql... IMHO that's a bad path to be walking down and the DRS stage just makes the slope a little more slippery by allowing stuff like that. Learn how to build robust jobs - native stages, separate links for inserts v. updates, generated sql, all that boring stuff. Plus side is they'll work fine and reject things in a predictable manner.
Throw in multiple actions in user-defined sql (something you failed to mention you were doing) and all bets are off. Obviously, it's not working 'fine as expected' or we wouldn't be here. Not sure how much help we can be in that situation, but perhaps if you posted your sql? Also, when do you have this 'issue' with rejects, only after the update fails and then the insert generates the FK violation or something else?
Throw in multiple actions in user-defined sql (something you failed to mention you were doing) and all bets are off. Obviously, it's not working 'fine as expected' or we wouldn't be here. Not sure how much help we can be in that situation, but perhaps if you posted your sql? Also, when do you have this 'issue' with rejects, only after the update fails and then the insert generates the FK violation or something else?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Of course not, nobody does - so check the database. That's what hashed files are for and the concepts of preloading and using them to determine insert versus update are the heart and soul of the Server product. Learn it. Love it. The people who come after you will thank you.
However, that's a whole 'nuther lesson in and of itself. If you want to pursue the reject issue, post your sqls and answer the questions in the last post. And for grins try changing REJECTED to REJECTEDCODE, curious what difference (if any) it makes in your case.
However, that's a whole 'nuther lesson in and of itself. If you want to pursue the reject issue, post your sqls and answer the questions in the last post. And for grins try changing REJECTED to REJECTEDCODE, curious what difference (if any) it makes in your case.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers