Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> is this a good question

is this a good question

From: Steve McClure <>
Date: Wed, 17 Sep 2003 12:04:48 -0800
Message-ID: <>

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

   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:

Author: Steve McClure

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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

Original text of this message