RE: how to capture the error when RID value not in the table (pl/ sql question)
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'GMei@xxxxxx'" <GMei@xxxxxx>, "Oracle-L (E-mail)" <Oracle-L@xxxxxxxxxxxxx>
- Date: Thu, 24 Feb 2005 08:11:13 -0500
Guang,
Well, you can do the obvious:
Select count(*)
Into l_count
From Customers
Where RID = p_CustomerRID;
If l_count = 0 then
RaiseError.NoRecordUpdated('Customers');
End if;
And then put the rest of your code after this.
Tom
-----Original Message-----
From: Guang Mei [mailto:GMei@xxxxxx]
Sent: Wednesday, February 23, 2005 1:23 PM
To: Oracle-L (E-mail)
Subject: how to capture the error when RID value not in the table (pl/sql
question)
I have the following code in a pl/sql procedure (oracle 9i), which is called
a lot.
-- RID is PK column on table Customers
-- p_name is a passed-in parameter
-- p_CustomerRID is a passed-in parameter
SAVEPOINT sp123;
UPDATE Customers
SET Name = p_name
WHERE RID = p_CustomerRID;
IF SQL%ROWCOUNT > 1 THEN
ROLLBACK WORK TO SAVEPOINT sp123;
RaiseError.TooManyUpdates('Customers');
ELSIF SQL%ROWCOUNT = 0 THEN
RaiseError.NoRecordUpdated('Customers');
END IF;
The code works fine now. But I would to change the update sql to
UPDATE Customers
SET Name = p_name
WHERE RID = p_CustomerRID
AND Name != p_name;
to avoid unnecessary redo writing. But if I check SQL%ROWCOUNT for this new
query and get 0, I would not know if it is due to a non-exist RID (Which I
want to capture this situation) or simply p_name already exists for that
RID. Is there a better way of doing this kind of thing, minimize redo
writing and capture the error the same time with only one sql run?
TIA.
Guang
*************************************************************************
PRIVILEGED AND CONFIDENTIAL:
This communication, including attachments, is for the exclusive use of
addressee and may contain proprietary, confidential and/or privileged
information. If you are not the intended recipient, any use, copying,
disclosure, dissemination or distribution is strictly prohibited. If you
are not the intended recipient, please notify the sender immediately by
return e-mail, delete this communication and destroy all copies.
*************************************************************************
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts: