Re: how to capture the error when RID value not in the table (pl/sql question)

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Thu, 24 Feb 2005 14:16:55 +0000
Message-ID: <7765c897050224061633ff874f@mail.gmail.com>

Hi
On Wed, 23 Feb 2005 13:22:57 -0500, Guang Mei <GMei_at_ph.com> 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.

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.
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.
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?

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;

I don't imagine this would be any more efficient.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 24 2005 - 09:19:59 CST