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

[Dasko, Dan]

You can't.  It appears that you want to select all the detailed text extries related to a specific problem.  You could define a cursor that selects all the detailed entries, or you could read all the detailed entries into a pl/sql table and then search through them.  If you use the cursor, then you have to reopen the cursor for each new problem.

 

Dan

-----Original Message-----
From: Wendy Y [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, April 03, 2001 2:51 PM
To: Multiple recipients of list ORACLE-L
Subject: Help - How to declare a cursor inside a FOR ... LOOP ?

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.
_____________________________________________________________________
This e-mail message has been scanned for the presence of all known computer viruses by the MessageLabs Virus Control Center. However, it is still recommended that you use local virus scanning software to monitor for the presence of viruses.