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

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Tue, 03 Apr 2001 11:59:46 -0700
Message-ID: <F001.002E0285.20010403114719@fatcity.com>

Wendy:

Actually, you don't need to declare the cursor inside the for loop, you just need to open it inside the loop and pass a parameter to the cursor.

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;  

    CURSOR AKLangTextCur (p_problem IN VARCHAR2) 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;     

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;

    OPEN AKLangTextCur(v_problem);
    LOOP

      FETCH AKLangTextCur into AKLangTextRec;
      EXIT WHEN AKLangTextCur%NOTFOUND;
      V_LONGTEXT_CUR := V_LONGTEXT_CUR ||AKLangTextRec.thisText;

    END LOOP;
    CLOSE AKLangTextCur;
    V_LONGTEXT := V_LONGTEXT || V_LONGTEXT_CUR;   END LOOP;
  V_LONGTEXT_TRIM := RTRIM(V_LONGTEXT, 2000); END;
/

Kevin
<snip>
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Toepke, Kevin M
  INET: ktoepke_at_cms.cendant.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Apr 03 2001 - 13:59:46 CDT