RE: how to capture the error when RID value not in the table (pl/ sql question)
- From: Guang Mei <GMei@xxxxxx>
- To: 'Niall Litchfield' <niall.litchfield@xxxxxxxxx>
- Date: Thu, 24 Feb 2005 09:58:41 -0500
Please see my comments in-line.
Guang
-----Original Message-----
From: Niall Litchfield [mailto:niall.litchfield@xxxxxxxxx]
Sent: Thursday, February 24, 2005 9:17 AM
To: GMei@xxxxxx
Cc: Oracle-L (E-mail)
Subject: Re: how to capture the error when RID value not in the table
(pl/sql question)
Hi
On Wed, 23 Feb 2005 13:22:57 -0500, Guang Mei <GMei@xxxxxx> wrote:
> 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?
This raises a few questions for me, that might help you think about this.
1. What purpose does the test for SQL%ROWCOUNT > 1 serve if RID is in
fact a PK. You won't ever get more than 1 record with the same RID.
[GM]: You are correct in theory that checking for SQL%ROWCOUNT > 1 is
uncessary. But I guess the orginal code (not written by me) wants to capture
any possible error. What happens if someone drops the PK constraints
accidently (I know this should not happen, at least in theory).
2. You say that the purpose is to save redo. How much redo do you save
per call? My guess would be that it was minimal.
[GM]: One call would not generate much redo. But I image if this query gets
called too many times and by too many sessions, then it is always good to
save any unnecessary redo writings.
3. If the amount of redo generated is a problem then how much is
caused by inefficient SQL per call and how much by too many calls?
[GM]: yes, I am going through lots of code and doing exactly what you
suggested.
And I'd also comment that if you capture the old name then you could always
do
if old_pname != p_name then
update UPDATE Customers
SET Name = p_name
WHERE RID = p_CustomerRID;
end if;
[GM]: To get old_pname, it requires another sql call, which I am trying to
avoid (don't know if it is possble though).
I don't imagine this would be any more efficient.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
*************************************************************************
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
Other related posts:
