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
