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

From: Guang Mei <GMei_at_ph.com>
Date: Thu, 24 Feb 2005 09:45:31 -0500
Message-ID: <7E62D965D357694C993D4F0E13B3C39B04D37F08@phexchange.ph.com>

This would require two sql calls (one the count check you said, then the actual update). In most cases, RID exists but Name != p_name, which means the update does happen. But sometimes, RID exists and Name already euuals to p_name. I was hoping to have just one sql call.

Guang

-----Original Message-----
From: Mercadante, Thomas F [mailto:thomas.mercadante_at_labor.state.ny.us] Sent: Thursday, February 24, 2005 8:11 AM To: 'GMei_at_ph.com'; Oracle-L (E-mail)
Subject: RE: how to capture the error when RID value not in the table (pl/ sql question)

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_at_ph.com]
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.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

************************************************************************* 
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.  
*************************************************************************
--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 24 2005 - 09:45:09 CST