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 Fri, May 14, 2010 at 3:30 AM, 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 -- 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
