Help - How to declare a cursor inside a FOR ... LOOP ?

[Wendy Y]

Hey, Guys:

I need to decalre a cursor inside a FOR .. LOOP, because the variables in SELECT statement for the CURSOR are coming from FOR .. LOOP. How could I do this?

DECLARE
  V_AKTIV_NR    aktiv.AKTIV_NR%TYPE;
  V_PROBLEM    aktiv.PROBLEM%TYPE;
  V_ENDDAT   aktiv.ENDDAT%TYPE;
  V_ENDUHR   aktiv.ENDUHR%TYPE;
  V_AUSSAGEW   aktiv.AUSSAGEW%TYPE;
  V_LANGTEXT   aktiv.LANGTEXT%TYPE;
  V_LONGTEXT    VARCHAR2(20000);
  V_LONGTEXT_CUR  VARCHAR2(13000);
  V_LONGTEXT_TRIM VARCHAR2(2000);
  p_PROBLEM    PROBLEM.PROBLEM%TYPE; 

############# This part need to be inside FOR.. LOOP, otherwise, I wouldn't get anything.

    CURSOR AKLangTextCur IS 
 SELECT  TO_CHAR(V_ENDDAT, 'YYYYMMDD')||' '||
   V_ENDUHR||' '||
  rtrim(V_AUSSAGEW)||' '||
  rtrim(V_LANGTEXT)   thisText
        FROM AKTIV
        WHERE V_PROBLEM = p_PROBLEM
        ORDER BY Aktiv_NR; 
    AKLangTextRec   AKLangTextCur%ROWTYPE;    

################################### Above
BEGIN
  FOR v_LoopIndex IN 1..pkgFreeText.v_NumEntries LOOP
    V_AKTIV_NR := pkgFreeText.V_AKTIV_NR_P(v_LoopIndex);
    V_PROBLEM  := pkgFreeText.V_PROBLEM_P(v_LoopIndex);
    V_ENDDAT   := pkgFreeText.V_ENDDAT_P(v_LoopIndex);
    V_ENDUHR   := pkgFreeText.V_ENDUHR_P(v_LoopIndex);
    V_AUSSAGEW := pkgFreeText.V_AUSSAGEW_P(v_LoopIndex);
    V_LANGTEXT := pkgFreeText.V_LANGTEXT_P(v_LoopIndex);

  SELECT PROBLEM INTO p_PROBLEM FROM PROBLEM;

########## CURSOR DECLARE should be HERE ##########

    BEGIN
     OPEN AKLangTextCur;
          LOOP
            FETCH AKLangTextCur into AKLangTextRec;
             EXIT WHEN AKLangTextCur%NOTFOUND;
            V_LONGTEXT_CUR := V_LONGTEXT_CUR ||AKLangTextRec.thisText;                
         END LOOP;
     CLOSE AKLangTextCur;
    END;                  
  V_LONGTEXT := V_LONGTEXT || V_LONGTEXT_CUR;  

  END LOOP;
   V_LONGTEXT_TRIM := RTRIM(V_LONGTEXT, 2000);

Thanks a lot for help

Wendy

---

Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.