Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

Re: is this a good question

From: Stephane Faroult <>
Date: Wed, 17 Sep 2003 13:09:40 -0800
Message-ID: <>

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


   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.


Stephane Faroult
Oriole Software
Please see the official ORACLE-L FAQ:
Author: Stephane Faroult

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 - 16:09:40 CDT

Original text of this message