is this a good practice...pl/sql question
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: is this a good practice...pl/sql question
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: is this a good practice...pl/sql question
Title: 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- From: Steve McClure [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 17, 2003 4:05 PM To: Multiple recipients of list ORACLE-L Subject: is this a good practice...pl/sql question 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: is this a good practice...pl/sql question
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. I guess I am really wondering if it is a good practice to modify the attributes of a record previously selected via a cursor. It is not just a typical variable that is defined and used in a manner that suits the algorithm. I am just wondering if it would be a better idea to treat this type of a record as a constant. To elevate it conceptually. The reason being that it does(should?)represent what was actually selected from the database. If another developer, or myself for that matter, were to come along at a later date, and use that attribute in another section of code not knowing, or forgetting, it had been altered above. I have actually used this technique extensively in a couple of routines more complex than the one I have described above. It was just as I made this little code change, I felt a pang of conscience, and wanted to ask this philosophical question. I am not worried about mucking up the database, or any such thing. I am aware that variable is just like any other attribute in any record I might have explicitly created myself. It is just that since the database instantiated this record to represent the results of my query, I am wondering if it should grant it an elevated postition. Sorry for rambling on and on etc. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: is this a good practice...pl/sql question
Title: RE: is this a good practice...pl/sql question Yes it is a good practice when required. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Steve McClure [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 17, 2003 5:45 PM To: Multiple recipients of list ORACLE-L Subject: RE: is this a good practice...pl/sql question 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. I guess I am really wondering if it is a good practice to modify the attributes of a record previously selected via a cursor. It is not just a typical variable that is defined and used in a manner that suits the algorithm. I am just wondering if it would be a better idea to treat this type of a record as a constant. To elevate it conceptually. The reason being that it does(should?)represent what was actually selected from the database. If another developer, or myself for that matter, were to come along at a later date, and use that attribute in another section of code not knowing, or forgetting, it had been altered above. I have actually used this technique extensively in a couple of routines more complex than the one I have described above. It was just as I made this little code change, I felt a pang of conscience, and wanted to ask this philosophical question. I am not worried about mucking up the database, or any such thing. I am aware that variable is just like any other attribute in any record I might have explicitly created myself. It is just that since the database instantiated this record to represent the results of my query, I am wondering if it should grant it an elevated postition. Sorry for rambling on and on etc. Steve -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Steve McClure INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
