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

From: William Beilstein <BeilstWH_at_obg.com>
Date: Tue, 03 Apr 2001 13:31:45 -0700
Message-ID: <F001.002E0688.20010403132356@fatcity.com>

It's actually easy. Following is an example

declare
cursor first is select work_id from my_table;

cursor second is select my_desc from my_desc_table where work_id = hold_work_id;

hold_work_id number(15);

begin
for rec in first loop
hold_work_id := rec.work_id;
for pnt in second loop

.... code to display desc line by referencinf pnt.my_desc .... end loop;
end loop;
end;

>>> Dan.Dasko_at_cdicorp.com 04/03/01 04:50PM >>>
You're probably right, I think I got stuck on the whole DECLARE BEGIN EXCEPTION END thing.

-----Original Message-----
Sent: Tuesday, April 03, 2001 4:36 PM
To: Multiple recipients of list ORACLE-L

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 Streeter
System Analyst
Abbott LBG
StreeLA2_at_hpd.abbott.com

-----Original Message-----
Sent: Tuesday, April 03, 2001 3:41 PM
To: Multiple recipients of list ORACLE-L

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-----
Sent: Tuesday, April 03, 2001 2:51 PM
To: Multiple recipients of list ORACLE-L

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 <http://personal.mail.yahoo.com/?.refer=mailiyfoot> 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.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: William Beilstein
  INET: BeilstWH_at_obg.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 - 15:31:45 CDT