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

From: Streeter, Lerone A LBX <StreeLA2_at_HPD.Abbott.com>
Date: Tue, 03 Apr 2001 12:35:05 -0700
Message-ID: <F001.002E040A.20010403123545@fatcity.com>

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 ?   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. Received on Tue Apr 03 2001 - 14:35:05 CDT