Home » SQL & PL/SQL » SQL & PL/SQL » doubt about returning
doubt about returning [message #346427] Mon, 08 September 2008 09:38 Go to next message
soujanya_srk
Messages: 111
Registered: November 2006
Location: HYDERABAD
Senior Member
in plsql origranm is it possible to update the rows, and also
see the rows that i have updated in a single sql statement
using returning clause?
Re: doubt about returning [message #346428 is a reply to message #346427] Mon, 08 September 2008 09:42 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Yes, but row by row is SLOW BY SLOW!
Re: doubt about returning [message #346435 is a reply to message #346427] Mon, 08 September 2008 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it is possible.

Regards
Michel
Re: doubt about returning [message #346627 is a reply to message #346428] Tue, 09 September 2008 03:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Be fair - the Op does say Rows - not row.

I suspect (and hope) that they're looging for this construct
UPDATE <table>
SET    <columns = values>
WHERE  <stuff>
RETURNING <columns> BULK COLLECT INTO <table>
Re: doubt about returning [message #348339 is a reply to message #346427] Tue, 16 September 2008 08:03 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
I tried it but i am getting error

UPDATE employee
set ename='HARI'
where empno=12
returning empno bulk collect into employee;

returning * bulk collect into employee
*
ERROR at line 4:
ORA-00936: missing expression


please tell em that where i am going wrong
Re: doubt about returning [message #348362 is a reply to message #348339] Tue, 16 September 2008 09:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
please tell em that where i am going wrong

"*" is wrong.

Regards
Michel
Re: doubt about returning [message #348489 is a reply to message #348339] Tue, 16 September 2008 23:38 Go to previous messageGo to next message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
Put the columns names in place of *
Re: doubt about returning [message #348490 is a reply to message #346427] Tue, 16 September 2008 23:38 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
UPDATE employee
set ename='HARI'
where empno=12
returning empno bulk collect into employee;

it is giving

returning empno bulk collect into employee
*
ERROR at line 4:
ORA-00925: missing INTO keyword

how can i overcome this problem
Re: doubt about returning [message #348492 is a reply to message #346427] Tue, 16 September 2008 23:41 Go to previous messageGo to next message
monikabhakuni
Messages: 110
Registered: September 2008
Location: India
Senior Member
i ma not using '*' i hev specified empno as the column name
Re: doubt about returning [message #348494 is a reply to message #348490] Tue, 16 September 2008 23:45 Go to previous message
navneet_sharma
Messages: 70
Registered: September 2008
Location: New Delhi, India
Member
u are bulk collecting into employee which is table name in your query.how can u bulk collect into a table. create pl/sql table collection like

declare
  type tbl_test is table of employee.empno%type;
  v_tbl_test  tbl_test;
begin
  update employee
  set....

  where...
  returning empno bulk collect into v_tbl_test;
  
  for i in 1.. v_tbl_test.count loop
    dbms_output.put_line(v_tbl_test(i));
  end loop;
end;


[Mod-edit: Frank added [code]-tags and formatted code to improve readability]

[Updated on: Tue, 16 September 2008 23:55] by Moderator

Report message to a moderator

Previous Topic: does not insert after resolving mutating problem
Next Topic: need a help
Goto Forum:
  


Current Time: Sat Dec 03 12:21:51 CST 2016

Total time taken to generate the page: 0.08628 seconds