its kinda slick. i have large tables. if i use the nested table with the cast function, I can use an index.
ill keep it in mind. http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=3748097f.5471527%40newshost.us.oracle.com&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3Doracle%2Bcast%2Bpl%252Fsql > > From: "Igor Neyman" <[EMAIL PROTECTED]> > Date: 2003/08/29 Fri PM 03:34:34 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: how to do a variable in-list of numbers? > > This method works; I have some code using it. > The only problem, it could be slow (on large tables), because index on > <code_column> will not help. > > Igor Neyman, OCP DBA > [EMAIL PROTECTED] > > > > -----Original Message----- > Chelur, Jayadas {PBSG} > Sent: Friday, August 29, 2003 10:45 AM > To: Multiple recipients of list ORACLE-L > > SELECT <columns> > FROM <sometable> > WHERE INSTR(','||<list_of_comma_separated_codes>||',' , > ','||<code_column>||',' ) > 0; > > > (1). Concatenate Commas at BOTH ENDS of the string containing > different code values. e.g. If the string is > '101,102,554,336,678,301,201,199' , the concatenate commas > on both ends to make it ',101,102,554,336,678,301,201,199,' > > this is to make sure that EVERY code ( including the first > and the LAST ) confirms to the pattern <,><value><,> > > (2) For each row in the table, use INSTR() function to see whether > the pattern <,><value of columna><,> is CONTAINED in the string > > INSTR() is used so that pattern matching can be used instead of > any range checking etc on the string. The actual code values in > the string can be IN ANY ORDER. > > -----Original Message----- > Sent: Friday, August 29, 2003 10:32 AM > To: Multiple recipients of list ORACLE-L > > > I need to do an insert select of the form > > insert into tab2 > select col1 > from tab1 > where col2 in (inlist of numbers); > > I do not know how many values will be in my inlist at runtime. With > strings > I just build a big string. How do I build an 'inlist' of numbers at > runtime? > > > Im using a cursor to determine which values need to be added to my > inlist. I > think I can do some kind of cast, but im not familiar with it. > > Im on 8i. I do not want to j ust run this inside my cursor. It could > then > execute 300-400 times and will run all day. > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: <[EMAIL PROTECTED] > 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: Chelur, Jayadas {PBSG} > 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: Igor Neyman > 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: <[EMAIL PROTECTED] 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).
