Home » SQL & PL/SQL » SQL & PL/SQL » workaround ORA-30926 (merge statement)
workaround ORA-30926 (merge statement) [message #212910] Mon, 08 January 2007 16:05 Go to next message
Messages: 201
Registered: March 2005
Senior Member
I really need help Sad I am trying to synchronize data from a remote database. This task involves merging 6 tables. Some tables are ok, but two of these tables do not primary keys on them nor do they have any unique identifier that would indicate that the record is unique. So, of course, when I use Oracle's merge command I get ORA-30926 unable to get a stable set of rows in the source tables. Here is my merge statement:

MERGE INTO pws_ssrtext n1
USING (SELECT ssrtext_term_code, ssrtext_crn, ssrtext_text
WHERE ssrtext_term_code IN ('200601', '200602')) e1
ON ( n1.pws_ssrtext_term_code = e1.ssrtext_term_code
AND n1.pws_ssrtext_crn = e1.ssrtext_crn)
n1.pws_ssrtext_text = e1.ssrtext_text
WHEN NOT MATCHED THEN INSERT ( pws_ssrtext_term_code, pws_ssrtext_crn, pws_ssrtext_text)
VALUES (e1.ssrtext_term_code, e1.ssrtext_crn, e1.ssrtext_text);

Is there a way to merge these records anyway? Like by the rowid or timestamp? thank you!!
Re: workaround ORA-30926 (merge statement) [message #212925 is a reply to message #212910] Mon, 08 January 2007 19:14 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Do you know for a fact that (pws_ssrtext_term_code, pws_ssrtext_crn) really is unique, even though there is no unique index or constraint?

If not, then what exactly were you expecting the MERGE to do when it tried to merge a row that existed twice?

If you know FOR A FACT that it is unique, then you can do the insert and update in separate steps. The INSERT can be done with a NOT IN sub-query to get only new rows, and the UPDATE can be done with an Updateable Join View (using the secret, unsupported, and highly dangerous BYPASS_UJVC hint).

Now you can stop reading at this point, because you only care that it runs without error - you don't care how dangerous and inappropriate the solution might be. If your join key is actually unique - just not enforced - then you have nothing to worry about using BYPASS_UJVC for a once-only SQL. Oracle might de-spport it in the future, but that doesn't matter because you didn't put it in production code - you used it this once only.

However, if you use that hint in repeatable production code, the programmer that inherits the system after you leave will hunt you down and fire-bomb your house, because you have left them with an unsupportable legacy that could crash their system.

Also, if the join key is not unique, then the duplicate rows will be updated in a non-deterministic fashion. ie. There are no guarantees what will happen to these rows. The only way to be sure is to run it, check the results are consistent with what you hoped, fix any errors where it didn't work, and then NEVER RUN IT AGAIN.

Ross Leishman
Re: workaround ORA-30926 (merge statement) [message #213382 is a reply to message #212925] Wed, 10 January 2007 09:01 Go to previous message
Messages: 201
Registered: March 2005
Senior Member
You are right, as always Smile Heh...I guess I would have to go with materialized views here...Thank you!!
Previous Topic: Is it possible to pass multiple values to a parameter in a procedure
Next Topic: Regards Locking of Objects
Goto Forum:

Current Time: Fri Aug 18 07:50:14 CDT 2017

Total time taken to generate the page: 0.29409 seconds