Home » SQL & PL/SQL » SQL & PL/SQL » Checking record collection type for existing data
Checking record collection type for existing data [message #219212] Tue, 13 February 2007 10:22 Go to next message
rbertrand
Messages: 45
Registered: November 2005
Member
hello everyone,
I've created a record and table type in a procedure. I need a way to check the table to see if the record I just retrieved from my cursor exists in the table before adding it. I've attached the code that I'm using. Any help would be greatly appreciated!!

Re: Checking record collection type for existing data [message #219250 is a reply to message #219212] Tue, 13 February 2007 12:20 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I haven't read your attached code, but there are generally two methods of handling the "insert it if it doesn't already exist, otherwise update it" situation.

The first and best is to use the merge statement.

The second is to attempt to do the insert, and in the exception handler for duplicate keys, do an update.
Re: Checking record collection type for existing data [message #219271 is a reply to message #219212] Tue, 13 February 2007 14:19 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
One of the most common problems I see with newbies is that you don't the ask question you really need to be asking. You make invalid assumptions and design inappropriate solutions as a result. I have yet to see a design where checking values in a cursor loop prior to DML is an appropriate solution. You should either be eliminating those rows by changing your cursor to not select them in the first place, or you should allow the unique index to throw the exception and handle that. If you are handling that by updating the row, then you might want think about using MERGE as suggested by smartin. The sample you provided is not sufficient enough give you a definite answer, which is another very common newbie error. Assume we can read. Provide all the info you have because you don't know yet what is important and more often than not, you remove the important stuff because you think it is irrelevant.
Re: Checking record collection type for existing data [message #219283 is a reply to message #219212] Tue, 13 February 2007 16:16 Go to previous messageGo to next message
rbertrand
Messages: 45
Registered: November 2005
Member
thanks for the response. I will re-evaluate my problem..
Re: Checking record collection type for existing data [message #219358 is a reply to message #219250] Wed, 14 February 2007 02:40 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If the majority of your records will exist, then this is quite an efficient solution:

Issue the Update statement.
Check SQL%Rowcount.
If it is 0 then you haven't updated a row, and you can safely do an insert.
Previous Topic: How to stringify a generic set of columns in a ROWTYPE variable?
Next Topic: create temporary tablespace ...; V.S create tablespace ... temporary;
Goto Forum:
  


Current Time: Fri Dec 02 19:06:49 CST 2016

Total time taken to generate the page: 0.09453 seconds