Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00036: maximum number of r (Oracle Apps 11.5.10)
ORA-00036: maximum number of r [message #303897] Mon, 03 March 2008 03:08 Go to next message
spmano1983
Messages: 269
Registered: September 2007
Senior Member
Friends,

i created form based on complex view and register in oracle apps also. I can able to insert the record.. but when i am trying to update the record its giving this error

"ORA-00036: maximum number of r"

I wrote INSTEAD OF trigger in databse

create or replace trigger order_lines_v_dml
 instead of insert or delete or update
 on dem_order_lines_v
 begin
  if inserting then
    insert into dem_order_lines
    (order_id, order_line_num,last_update_date,last_updated_by,
    creation_date,created_by,last_update_login,product_id,gl_account_cc_id,ordered_quantity) values
    (:new.order_id,:new.order_line_num,:new.last_update_date,:new.last_updated_by,
    :new.creation_date,:new.created_by,:new.last_update_login,:new.product_id,
    :new.gl_account_cc_id,:new.ordered_quantity);
  end if;
  if updating then
   update dem_order_lines_v set
   order_id=:new.order_id,order_line_num=:new.order_line_num,
   last_update_date=:new.last_update_date,last_updated_by=:new.last_updated_by,
   creation_date=:new.creation_date, created_by=:new.created_by,
   last_update_login=:new.last_update_login, product_id=:new.product_id,
   gl_account_cc_id=:new.gl_account_cc_id,
   ordered_quantity=:new.ordered_quantity;
 end if;
 end;


Any helps?

Thanks
Manoharan
Re: ORA-00036: maximum number of r [message #303998 is a reply to message #303897] Mon, 03 March 2008 11:41 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
if updating then
   update dem_order_lines_v set
   order_id=:new.order_id,order_line_num=:new.order_line_num,
   last_update_date=:new.last_update_date,last_updated_by=:new.last_updated_by,
   creation_date=:new.creation_date, created_by=:new.created_by,
   last_update_login=:new.last_update_login, product_id=:new.product_id,
   gl_account_cc_id=:new.gl_account_cc_id,
   ordered_quantity=:new.ordered_quantity;
end if;
Where is the where condition? How many rows your are trying to update in the trigger for each row of the update of the view.

By
Vamsi
Re: ORA-00036: maximum number of r [message #304001 is a reply to message #303897] Mon, 03 March 2008 11:46 Go to previous message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-00036: maximum number of recursive SQL levels (string) exceeded
Cause: An attempt was made to go more than the specified number of recursive SQL levels.
Action: Remove the recursive SQL, possibly a recursive trigger.

You update dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates dem_order_lines_v that fires order_lines_v_dml which updates...

Do you see what I mean?

Regards
Michel
Previous Topic: stopping Oracle seeing a field name as a function
Next Topic: ORA-01427: single-row subquery returns more than one row
Goto Forum:
  


Current Time: Fri Dec 09 11:42:03 CST 2016

Total time taken to generate the page: 0.26010 seconds