Hi ,
I'm using Oracle 10g Version
I tried the below code using UPDATE with Returning Clause & MERGE with Returning Clause .
I found NO errors while working with UPDATE statement . The following is the code with UPDATE statement
DECLARE
TYPE empno_list IS TABLE OF emp.empno%TYPE;
vempno_list empno_list;
BEGIN
UPDATE emp
SET comm = 11
WHERE deptno IN (SELECT deptno FROM dept)
RETURNING empno
BULK COLLECT INTO vempno_list;
FOR i IN vempno_list.FIRST .. vempno_list.LAST
LOOP
DBMS_OUTPUT.put_line ('Values of EMP ' || vempno_list (i));
END LOOP;
END;
But getting the error PL/SQL: ORA-00933: SQL command not properly ended when working with MERGE Statement
declare
type empno_list is table of emp.empno%type;
vempno_list empno_list;
begin
merge into emp tgt
using dept src
on (src.deptno =tgt.deptno)
when matched then
update set tgt.comm=12
returning tgt.empno bulk collect into vempno_list ;
for i in vempno_list.first .. vempno_list.last loop
dbms_output.put_line('Values of EMP '||vempno_list(i) ) ;
end loop;
end;
Please suggest me