| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: is this a good practice...pl/sql question
Steve,
Nothing wrong with setting xrec.state to null ... your developer is avoiding hard coding of NULL in the insert statement. In fact if this is working fine then only thing I'd try to change is bulk inserts instead of one by one ...
Don't worry .... it is normal.
Raj
-----Original Message-----
Sent: Wednesday, September 17, 2003 4:05 PM
To: Multiple recipients of list ORACLE-L
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jamadagni, Rajendra
INET: Rajendra.Jamadagni_at_espn.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).
 |
 |