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: [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).
