Hi, I see that you skipped over my suggestion. It looks the same, but it is NOT the same as the one you cited. The suggestion you tried simply added "declare", "begin" and "end" statements, which is not enough. The problem is that you are using SELECT .. INTO in your cursor which is probably not necessary. Instead, assign an appropriate alias.
My original post: Roopesh, First of all cursors belong in the declare block of a pl/sql call. Also, I find it odd that from within the cursor you are assigning values to variables... this isn't necessary. I changed your SELECT...INTO statement to simply assign aliases to the columns you are selecting from: DECLARE CURSOR seg_high_low is select CONCATENATED_SEGMENTS_HIGH AS seg_high, CONCATENATED_SEGMENTS_LOW AS seg_low from TP1_EDW_SOR.EGL_CROSS_VLD_RULES where trim(upper(COA_ID)) = ncoa_id and INCL_EXCL_IND = 'E'; ... put your other variable assignments here ... BEGIN ... put your other code here ... OPEN seg_high_low; while(seg_high_low%FOUND) LOOP IF ((norg_seg < substr(seg_high_low.seg_low, 0, 3)) OR (norg_seg > substr(seg_high_low.seg_high, 0, 3))) then num := 1; ELSIF ((nnml_seg < substr(seg_high_low.seg_low, 4, 8)) OR (nnml_seg > substr(seg_high_low.seg_high, 4, 8))) then num := 2; ELSIF ((cntpr_seg < substr(seg_high_low.seg_low, 9, 12)) OR (cntpr_seg > substr(seg_high_low.seg_high, 9, 12))) then num := 3; END; -- good luck! -- Rich On Sun, May 16, 2010 at 9:47 PM, Roopesh S <[email protected]> wrote: > Hi, > > sorry for delay response.. i was out sick.... > > i tried below suggestion but no luck..... :( > > i am doing "num := 1, 2, 3...." etc ... this just to print the value of num > to knw which seg falied to satisfy the condition.. > > any other idea what might be the issue..i still get same error message... : > > "PLS-00225: subprogram or cursor 'SEG_HIGH_LOW' reference is out of scope" > > > > > On Fri, May 14, 2010 at 4:04 PM, Thiago Santana <[email protected]>wrote: > >> Try: >> >> *DECLARE* >> >> CURSOR seg_high_low is >> select CONCATENATED_SEGMENTS_HIGH, CONCATENATED_SEGMENTS_LOW into >> seg_high, seg_low from TP1_EDW_SOR.EGL_CROSS_VLD_RULES where >> trim(upper(COA_ID)) = ncoa_id and INCL_EXCL_IND = 'E'; >> *BEGIN* >> OPEN seg_high_low; >> while(seg_high_low%FOUND) >> LOOP >> IF ((norg_seg < substr(seg_high_low.seg_low, 0, 3)) OR >> (norg_seg > substr(seg_high_low.seg_high, 0, 3))) then >> num := 1; >> ELSIF ((nnml_seg < substr(seg_high_low.seg_low, 4, 8)) OR >> (nnml_seg > substr(seg_high_low.seg_high, 4, 8))) then >> num := 2; >> ELSIF ((cntpr_seg < substr(seg_high_low.seg_low, 9, 12)) OR >> (cntpr_seg > substr(seg_high_low.seg_high, 9, 12))) then >> num := 3; >> END LOOP; >> >> *END;* >> On 14 May 2010 07:30, Roopesh S <[email protected]> wrote: >> >>> >>> hi, >>> >>> Following is the cursor created >>> >>> CURSOR seg_high_low is >>> select CONCATENATED_SEGMENTS_HIGH, CONCATENATED_SEGMENTS_LOW into >>> seg_high, seg_low from TP1_EDW_SOR.EGL_CROSS_VLD_RULES where >>> trim(upper(COA_ID)) = ncoa_id and INCL_EXCL_IND = 'E'; >>> OPEN seg_high_low; >>> while(seg_high_low%FOUND) >>> >>> LOOP >>> IF ((norg_seg < substr(seg_high_low.seg_low, 0, 3)) OR (norg_seg > >>> substr(seg_high_low.seg_high, 0, 3))) then >>> num := 1; >>> ELSIF ((nnml_seg < substr(seg_high_low.seg_low, 4, 8)) OR (nnml_seg > >>> substr(seg_high_low.seg_high, 4, 8))) then >>> num := 2; >>> ELSIF ((cntpr_seg < substr(seg_high_low.seg_low, 9, 12)) OR (cntpr_seg > >>> substr(seg_high_low.seg_high, 9, 12))) then >>> num := 3; >>> >>> when i run the code.. it says... >>> >>> >>> PLS-00225: subprogram or cursor 'SEG_HIGH_LOW' reference is out >>> of scope >>> >>> ANY IDEA.. whats wrong here.... >>> >>> -- >>> Thanx'N'Regards >>> Roope >>> >>> -- >>> You received this message because you are subscribed to the Google >>> Groups "Oracle PL/SQL" group. >>> To post to this group, send email to [email protected] >>> To unsubscribe from this group, send email to >>> [email protected] >>> For more options, visit this group at >>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>> >> >> >> >> -- >> Thank you, >> TS >> +55 21 7524-9415 >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to [email protected] >> To unsubscribe from this group, send email to >> [email protected] >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> > > > > -- > Thanx'N'Regards > Roopesh > > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to [email protected] > To unsubscribe from this group, send email to > [email protected] > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
