Can't combine WHERE CURRENT OF and RETURNING in Update

[bogman]

Hi,

Can anyone out there explain why I am unable to combine WHERE CURRENT OF and a RETURNING Clause in an UPDATE statement ( in an anonymous PL/SQL Block ). The following is an example of what I am trying to do:

...
...

UPDATE orders
SET order_date = order_date + 7
WHERE CURRENT OF c_ords
RETURNING order_nr INTO v_ord.order_nr ;

...
...

The cursoe c_Ords has been previously declared and the UPDATE statement is embedded in a FOR cursor LOOP.

I get the following error message when trying to compile the code in PL/SQL Developer:

PL/SQL:ORA-00933. SQL command not properly ended.

This isn't a showstopper but nevertheless I'd be very grateful if someone could shed some light on why this is happening. I'm using Oracle 10g,

regards,

Bogman

 

[oradba101]

I believe you have to put a semi-colon at the end of the WHERE clause line

Regards,



William Chadbourne
Oracle DBA

 

[jimirvine]

Hi

try

UPDATE orders
SET order_date = order_date + 7
WHERE CURRENT OF c_ords;
 v_ord.order_nr := order_nr

 

[bogman]

Hi,

Thanks for the quick feedback however neither response seems to give me the answer I was hoping for. I don't need a workaround, rather I would like to know why I can't combine WHERE CURRENT OF and a RETURNING clause in the UPDATE statement. The example I have chosen is just a random staetment to illustrate the issue,

Cheers

 

[jimirvine]

rather I would like to know why I can't combine WHERE CURRENT OF and a RETURNING clause in the UPDATE statement.

I believe that this is because it would be incorrect syntax. i.e. The RETURNING clause is not used when looping through Explicit cursors unless you were doing something like

for i in c1 loop
update t set salary = salary +1
where employee_id = 100
returning last_name into x;
end loop;
end;

(which would be a little bizzarre)
The equivalent of what you are actually trying to do would be something like:

declare 
cursor c1 is select t.*, t.rowid ri from t;
x varchar2(300);
begin
for i in c1 loop
update t set salary = salary +1
where rowid = i.ri
returning last_name into x;
end loop;
end;

The reference of rowid is the equivalent of the 'WHERE CURRENT OF' clause

 

[bogman]

Hi Jim,

Thanks again for the feedback. How about this scenario? I'm processing a list of employees using an explicit cursor and I'm updating the salary of the employees based on some criteria previously worked out. I want to capture the new salary of each employee updated using the following type of syntax:

...
...

FOR i IN c_emp LOOP

UPDATE employee
SET salary = salary * v_incr
WHERE CURRENT OF c_emp
RETURNING salary INTO v_new_salary ;

...
...

Now I know this could be done in a different way but I would still like to know the theory behind why the INCLUSION of WHERE CURRENT OF rather than 'WHERE employee_nr = v_emp.employee_nr' makes the syntax incorrect?

 

[jimirvine]

Hmm, don't know the theory of it to be honest, maybe someone else can jump in?

 

[Dagon]

It's entirely possible there is no technical reason. It could just be an "unsupported feature" (i.e. bug). Sometimes you just have to live with these things and find another way to do it. As previous contributors have mentioned, the "where current of" functionality can easily be duplicated using rowid.

 

[bogman]

Ok,

Thanks everyone for your contributions