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

comments also in line. (plus a bit of editing) On Thu, 24 Feb 2005 09:58:41 -0500, Guang Mei <GMei_at_ph.com> wrote: <snip>
> 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).

What happens if someone updates the tables using some mechanism other than your package? bulk updates etc?

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

Well one approach is certainly to minimize the work done by the app, but another would be to minimize the number of calls. We have a (3rd party) helpdesk package, it stores application metadata - screen definitions, language strings etc- in the db. When loading it retrieves them one at a time. It always uses a PK lookup so each call is very fast. Retrieving 3000 rows in 3000 calls isn't. Too many calls to the same package would have the same effect, in the example you are giving you are updating a name when potentially it didn't change, wouldn't good design say that the update routine never got called in the first place if the user didn't change the name? Admittedly it is an update routine and not a delete and insert routine!  

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

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