| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> is this a good practice...pl/sql question
Allright I am making some changes to some pl/sql code that handles batch
inserts into the database. I am making changes to correct an error where
our clients are sending us data with invalid state information in their
address fields. A constraint prohibits the insert with records with invalid
states, nulls are however allowed. The decision was made to insert the rest
of the address information, leaving the state column null. OK that is
enough background. Here is an example of how I am handling this.
for x_rec in driving_cur loop
if (x_rec.state IS INVALID)then --pseudo coded to hide embarrassing design implementation
x_rec.state:=NULL;
end if;
insert into address(other_columns,..state)
values(x_rec.other_cols,...x_rec.state);
end loop;
I know I am asking a best practices question, and blatantly using old fashioned insert in the middle of a loop style code. This was originally developed in 7.3, and hasn't been recoded to take advantage of the bulk enhancements.
My question is regarding the practice of changing the value of a record's attribute(setting x_rec.state to null) after I have selected that record in a cursor. I have been doing this for some time, and it just dawned on me that it might not be a good idea to do this. My thinking is it might be confusing to a developer, or the fear that at some point Oracle might say.."that was obviously not an intended feature, that usage no longer allowed". I am wondering if instead I should test the state column of the record and then assign that value or NULL to a local variable. I would then insert the local variable instead of the attribute from the record.
Just sort of a bouncing the ball off the wall here, in fact I think I may have resolved the question internally while asking it. In any case I am wondering what others think.
Steve McClure
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Steve McClure
INET: smcclure_at_usscript.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Sep 17 2003 - 15:04:48 CDT
 |
 |