Steve McClure wrote: > > 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 >
Steve, Keeping aside all considerations about the loop, I see no problem here. The cursor variable is just short-hand notation for defining a table%ROWTYPE - you are not modifying some hidden Oracle internal state if this is what you are fearing. No 'mutating cursor', if I guess you correctly. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
