Home » SQL & PL/SQL » SQL & PL/SQL » RETURNING clause returns wrong value upon UPDATE statement (Oracle 11g)
RETURNING clause returns wrong value upon UPDATE statement [message #677484] Mon, 23 September 2019 09:38 Go to next message
ermoadam
Messages: 3
Registered: September 2019
Junior Member
Hello all,

I have encountered an error upon using RETURNING clause on UPDATE statement. Please find the corresponding block of code that produces the error down below:

UPDATE billable_accounts
SET npmt = 5189
WHERE ba_number='005086708'
RETURNING cust_seq_id INTO v_cust_id;

Also, note the following information for the above statement:

billable_accounts: View constructed by 3 tables (lets say a, b, c)
npmt: NUMBER(22) - from table a
cust_seq_id: NUMBER(18) - from table b
ba_number: VARCHAR2(71 Byte) - from table c

cust_seq_id gets the value of npmt column (5189 - that is updated through the statement). However, cust_seq_id value should be 456668 instead of 5189.

Also, you may find attached a screenshot that proves the above. Has anyone else faced something similar to this?

Thank you,
Ermo.
Re: RETURNING clause returns wrong value upon UPDATE statement [message #677486 is a reply to message #677484] Mon, 23 September 2019 09:56 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
We would need to see the view definition, along with the code of any triggers on either the underlying tables or the view itself, to have any chance of working out what is going on.
Re: RETURNING clause returns wrong value upon UPDATE statement [message #677489 is a reply to message #677486] Mon, 23 September 2019 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

We also need the reseult of the following query:
select * from v$version;

Re: RETURNING clause returns wrong value upon UPDATE statement [message #677512 is a reply to message #677489] Tue, 24 September 2019 02:04 Go to previous messageGo to next message
ermoadam
Messages: 3
Registered: September 2019
Junior Member
Hello all,

Attached the requested information.

BR
Re: RETURNING clause returns wrong value upon UPDATE statement [message #677513 is a reply to message #677489] Tue, 24 September 2019 02:05 Go to previous messageGo to next message
ermoadam
Messages: 3
Registered: September 2019
Junior Member
Also the DB version info.
Re: RETURNING clause returns wrong value upon UPDATE statement [message #677515 is a reply to message #677512] Tue, 24 September 2019 02:21 Go to previous message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is only the view definition.
Is there any trigger?
What are the definition of the tables?

Note that many of us can't or don't want to download files so post this inline inside the post and formatted (see How to use [code] tags and make your code easier to read).

Previous Topic: How to call compiled procedure and function in a procedure
Next Topic: display issue
Goto Forum:
  


Current Time: Tue Sep 29 22:43:30 CDT 2020