Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: is this a good practice...pl/sql question

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

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Wed, 17 Sep 2003 12:19:45 -0800
Message-ID: <F001.005D03FF.20030917121945@fatcity.com>


Steve,

        It may be "old fashion" code, but if it works within the time frame it needs to run in, why spend time recoding? At any rate, I don't see a problem.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----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: Goulet, Dick
  INET: DGoulet_at_vicr.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). Received on Wed Sep 17 2003 - 15:19:45 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US