Home » SQL & PL/SQL » SQL & PL/SQL » MERGE statment in oracle pl/sql (Oracle 10g)
MERGE statment in oracle pl/sql [message #428796] Fri, 30 October 2009 02:02 Go to next message
balaji123
Messages: 29
Registered: October 2009
Location: sanfrancisco
Junior Member
Hi,
I am able to use oracle merege statment without any issues.but i have 2 merge statement, i don't want to partial commit.


BEGIN
--- step 1
Begin
Merge 1
exception

end;

-- step 2
BEGIN
merge 2
exception
end ;
--- if step 1 & step2 success then comitt else rollback;
--- how to check both merge statement success
END;

Please do the needful asp. I got stuck more than week.

Best Regards,
Bal
Re: MERGE statment in oracle pl/sql [message #428797 is a reply to message #428796] Fri, 30 October 2009 02:07 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
SQL> DECLARE
  2
  3     TYPE aat_id IS TABLE OF source_table.object_id%TYPE
  4        INDEX BY PLS_INTEGER;
  5     TYPE aat_name IS TABLE OF source_table.object_name%TYPE
  6        INDEX BY PLS_INTEGER;
  7     TYPE aat_type IS TABLE OF source_table.object_type%TYPE
  8        INDEX BY PLS_INTEGER;
  9
 10     aa_ids   aat_id;
 11     aa_names aat_name;
 12     aa_types aat_type;
 13
 14     CURSOR c_source IS
 15        SELECT object_id
 16        ,      object_name
 17        ,      object_type
 18        FROM   source_table;
 19
 20     n PLS_INTEGER := 0;
 21
 22  BEGIN
 23
 24     OPEN c_source;
 25     LOOP
 26        FETCH c_source BULK COLLECT INTO aa_ids, aa_names, aa_types LIMIT 1000;
 27
 28        --[some processing]--
 29
 30        FORALL i IN 1 .. aa_ids.COUNT
 31           MERGE
 32              INTO  target_table tgt
 33              USING (
 34                     SELECT aa_ids(i) AS object_id
 35                     ,      aa_names(i) AS object_name
 36                     ,      aa_types(i) AS object_type
 37                     FROM   dual
 38                    ) src
 39              ON   ( src.object_id = tgt.object_id )
 40           WHEN MATCHED
 41           THEN
 42              UPDATE
 43              SET   tgt.object_name = src.object_name
 44              ,     tgt.object_type = src.object_type
 45           WHEN NOT MATCHED
 46           THEN
 47              INSERT ( tgt.object_id
 48                     , tgt.object_name
 49                     , tgt.object_type )
 50              VALUES ( src.object_id
 51                     , src.object_name
 52                     , src.object_type );
 53
 54        n := n + SQL%ROWCOUNT;
 55
 56        EXIT WHEN c_source%NOTFOUND;
 57     END LOOP;
 58     CLOSE c_source;
 59
 60     DBMS_OUTPUT.PUT_LINE(n || ' rows merged.');
 61  END;
 62  /
86889 rows merged.

PL/SQL procedure successfully completed.

Avoid use of merge and replace it by UPDATE INSERT

Quote:

One DBA notes that it's sometimes better to write your own procedural alternative:

"If there is an alternative mechanism for avoiding the MERGE then I'd be very glad to hear it, but this method may stand on its own if the alternatives do not allow direct path operations on the MV table or the sorting of the table data."

Also, Adrian Billington notes that a custom SQL procedure is faster than the merge statement:

"We can see that MERGE performed less well than our two-part SQL solution; with it taking over twice as long. It generated more redo and used more latches."


The Oracle "merge" statement combines an insert and update which is why it's also called an "upsert". At a high level the merge statement simplifies the syntax, which is essentially a simple "insert, if already exists, update".

Sadly, there are some serious performance issues with MERGE, as noted here.


Some more links for your help:
http://www.oracle-developer.net/display.php?id=203
http://oraclesponge.wordpress.com/2005/11/23/optimizing-materialized-views-part-iii-manual-refresh-mechanisms/

[Updated on: Fri, 30 October 2009 02:38]

Report message to a moderator

Re: MERGE statment in oracle pl/sql [message #428805 is a reply to message #428797] Fri, 30 October 2009 02:42 Go to previous message
balaji123
Messages: 29
Registered: October 2009
Location: sanfrancisco
Junior Member
thank you so much for your reply, Please give me some example.

We don't want to handle individual commit/ rollback on lower level in each merge statement code.

we have nearly 23 merge statement in pl/sql block , at the top level we want to commit all , if all the merge statement is success
( no partial commit)

1) how to track merge statement error message in pl/sql block without error lock table.cause we want to send that information as ref cursor to java front end.

2)while using merge statement , oracle will apply locks by default or how to lock a row level records, cause it is a oltp system?

thanks in advance.Please do the needful.

Best Regards,
Bal
Previous Topic: start stop rowcount in query
Next Topic: cursor not fetching long column
Goto Forum:
  


Current Time: Thu Sep 29 17:35:42 CDT 2016

Total time taken to generate the page: 0.08415 seconds