Home » SQL & PL/SQL » SQL & PL/SQL » plsql (oracle 10 g , Windows7)
plsql [message #599572] Sat, 26 October 2013 04:57 Go to next message
saturnel
Messages: 8
Registered: October 2013
Junior Member

Good morning everybody
please i try to execute this code but i have an error please help me to solve my pb i don't understand please

clear screen
set serveroutput on;

declare
empno employees.employee_id%type:=100;

begin

merge into copy_emp c
using employees e
on (e.employee_id = c.empno)
when matched then

update set
c.firstname = e.firstname,
c.last_name = e.lastname,
c.email= e.email,
c.phone_number = e.phone_number,
c.hire_date = e.hire_date,
c.job_id = e.job_id,
c.salary = e.salary,
c.commission_pct = e.commission_pct,
c.manager_id = e.manager_id,
c.department_id = e.department_id;

when no matched then
insert values(e.employee_id, e.firstname, e.lastname, e.email, e.phone_number, e.hire_date, e.job_id, e.salary,
e.commission_pct, e.manager_id, e.manager_id, e.department_id);
end;
/



Error report:
ORA-06550: line 23, column 5:
PLS-00103: Encountered the symbol "WHEN" when expecting one of the following:

begin case declare end exception exit for goto if loop mod
null pragma raise return select update while with
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Re: plsql [message #599573 is a reply to message #599572] Sat, 26 October 2013 05:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2405
Registered: May 2013
Location: World Wide on the Web
Senior Member
1. You should post this in SQL/PLSQL forum.

2. If you see the error message, it clearly shows line number 23, the merge statement has syntax error. Remove the semi-colon at the end of update statement.

3. There is another syntax error in the merge statement, it's incorrect "when no matched". Correct it to -

WHEN NOT MATCHED


Read this, http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm

Regards,
Lalit

[Updated on: Thu, 06 March 2014 13:17] by Moderator

Report message to a moderator

Re: plsql [message #599578 is a reply to message #599572] Sat, 26 October 2013 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59276
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

c.department_id = e.department_id;


No ";" at the end of this line.

no matched 


It is "not matched".

In the end, you have not to use PL/SQL for a single SQL statement.

Re: plsql [message #599590 is a reply to message #599578] Sat, 26 October 2013 09:39 Go to previous message
saturnel
Messages: 8
Registered: October 2013
Junior Member

THANK YOU FOR ALL MY DEAR.
Previous Topic: Another dynamic sql using dbms_sql.execute
Next Topic: Getting Hostname,systimestamp over db link
Goto Forum:
  


Current Time: Tue Sep 30 11:05:52 CDT 2014

Total time taken to generate the page: 0.13128 seconds