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

From: Dasko, Dan <Dan.Dasko_at_cdicorp.com>
Date: Tue, 03 Apr 2001 12:47:03 -0700
Message-ID: <F001.002E049A.20010403125044@fatcity.com>

You're
probably right, I think I got stuck on the whole DECLARE BEGIN EXCEPTION END thing.

  <FONT face=Tahoma
  size=2>-----Original Message-----From: Streeter, Lerone A LBX   [mailto:StreeLA2_at_HPD.Abbott.com]Sent: Tuesday, April 03, 2001 4:36   PMTo: Multiple recipients of list ORACLE-LSubject: RE:   Help - How to declare a cursor inside a FOR ... LOOP ?   i
  speak from ignorance, isn't there implicit and explicit cursor declarations   and usage?  can't you encapsulate that section you have identified in a   loop and it will implicitly opens a cursor?  then all you have to do is   manipulate as needed to generate desired results...    

  ===========================================Lerone 

  StreeterSystem AnalystAbbott

  LBGStreeLA2_at_hpd.abbott.com=========================================== 
  
  

<FONT face=Tahoma

    size=2>-----Original Message-----From: Dasko, Dan     [mailto:Dan.Dasko_at_cdicorp.com]Sent: Tuesday, April 03, 2001 3:41     PMTo: Multiple recipients of list ORACLE-LSubject: RE:     Help - How to declare a cursor inside a FOR ... LOOP ?
<SPAN

    class=869183918-03042001>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.
<SPAN

    class=869183918-03042001> 
<SPAN

    class=869183918-03042001>Dan     

      <FONT face=Tahoma 
      size=2>-----Original Message-----From: Wendy Y 
      [mailto:lannyue_at_yahoo.com]Sent: Tuesday, April 03, 2001 2:51 
      PMTo: Multiple recipients of list ORACLE-LSubject: 
      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;     
      ################################### <FONT 
      size=2>AboveBEGIN  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!?<A 
      href="http://personal.mail.yahoo.com/?.refer=mailiyfoot">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. 
  _____________________________________________________________________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.    Received on Tue Apr 03 2001 - 14:47:03 CDT