Re: is this a good practice...pl/sql question

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