| Checking record collection type for existing data [message #219212] |
Tue, 13 February 2007 10:22  |
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  |
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 |
scottwmackey
Messages: 515
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
