Home » SQL & PL/SQL » SQL & PL/SQL » exception with merge (oracle 11g)
exception with merge [message #576422] Mon, 04 February 2013 15:53 Go to next message
swapnabpnn
Messages: 96
Registered: December 2010
Member
Hi I am trying to implement following query by using merge where if there is a match then update need to be happened if not exception should be raised.
SQL> create or replace PACKAGE BODY pkg
  2  FUNCTION f RETURN NUMBER IS
  3  v_proc_name varchar2(40) := 'S';
  4  v_row_count number := 0;
  5  no_match EXCEPTION;
  6  BEGIN
  7  dbms_output.put_line('-----------------------------------------------------
-------');
  8  dbms_output.put_line('Process: ' || v_proc_name);
  9  dbms_output.put_line('-----------------------------------------------------
-------');
 10  dbms_output.put_line('Process started at ' ||
 11  to_char(SYSDATE, 'mm/dd/yyyy hh24:mi:ss'));
 12  dbms_output.put_line('-----------------------------------------------------
-------');
 13  dbms_output.put_line('update missing fields to t');
 14  MERGE INTO tar t --line 32
 15  USING (SELECT x,y,z
 16  FROM src) s
 17  ON (t.a = s.b AND t.c = s.d)
 18  WHEN MATCHED THEN
 19  UPDATE
 20  SET t.e = s.x,
 21  t.f =s.y,
 22  t.g=s.z
 23  WHEN NOT MATCHED THEN
 24  raise no_match; --line 59
 25  v_row_count := SQL%ROWCOUNT;
 26  COMMIT;
 27  RETURN 0;
 28  EXCEPTION
 29  WHEN no_match THEN
 30  DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------
-------');
 31  dbms_output.put_line ('There is no match for the records in source and targ
et');
 32  DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------
-------');
 33  DBMS_OUTPUT.PUT_LINE('Process Ended at '||TO_CHAR(sysdate,'MM/DD/YYYY HH24:
MI:SS'));
 34  RETURN - 1;
 35  RAISE;
 36  WHEN OTHERS THEN
 37  dbms_output.put_line('Process Ended at: ' || to_char(sysdate,'YYYYMMDD HH24
:MI')) ;
 38  dbms_output.put_line('Error message: ' || sqlcode || sqlerrm) ;
 39  dbms_output.put_line(rtrim(dbms_utility.format_error_stack,chr(10))) ;
 40  dbms_output.put_line(rtrim(dbms_utility.format_error_backtrace,chr(10))) ;
 41  dbms_output.new_line;
 42  ROLLBACK ;
 43  RETURN - 1;
 44  RAISE;
 45  END f;
 46  END pkg;
 47  /

Warning: Package Body created with compilation errors.

When I try to run the above package body i am getting error as
LINE/COL ERROR
-----------------------------------------------------------------
14/1 PL/SQL: SQL Statement ignored
24/1 PL/SQL: ORA-00905: missing keyword


Please help me with this.

Thanks
Re: exception with merge [message #576436 is a reply to message #576422] Tue, 05 February 2013 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is unreadable, please INDENT the code.
And explain with words what is the purpose of all this code, there is no need for us to waste our time to reverse engineer your code when you can simply explain us its purpose.

Regards
Michel
Re: exception with merge [message #576449 is a reply to message #576422] Tue, 05 February 2013 03:13 Go to previous messageGo to next message
mvmkandan
Messages: 67
Registered: May 2010
Location: Trivendrum
Member
WHEN NOT MATCHED THEN
   [b]RAISE no_match; --line 59[/b]   v_row_count := SQL%ROWCOUNT;
   COMMIT;


Error occures here.... The syntax of Merge statement will be

Merge <<>>
WHEN Matched Then UPDATE ....
WHEN UNMATCHED THEN INSERT ....

like this only. So after WHEN UNMATCHED, INSERT should be given.

If you want to do only update then what is the use of MERGE...

Veera
Re: exception with merge [message #576466 is a reply to message #576422] Tue, 05 February 2013 05:05 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Why don't you use simple IF ELSE instead of MERGE?

regards,
Delna
Re: exception with merge [message #576471 is a reply to message #576449] Tue, 05 February 2013 05:31 Go to previous message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you want to do only update then what is the use of MERGE...


Maybe because MERGE accepts more cases than UPDATE an inline view.

Regards
Michel

[Updated on: Tue, 05 February 2013 05:36]

Report message to a moderator

Previous Topic: Cursor not fetching special character
Next Topic: Assigning an ID
Goto Forum:
  


Current Time: Wed Oct 22 00:22:53 CDT 2014

Total time taken to generate the page: 0.12956 seconds