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

From: Bala, Prakash <prakash.bala_at_cingular.com>
Date: Wed, 04 Apr 2001 12:11:18 -0700
Message-ID: <F001.002E1AD0.20010404121623@fatcity.com>

Probably you are exceeding the length of the PL/SQL variable when you are assigning values to it.

Check the length by inserting the following line in your code:

dbms_output.put_line(length(V_LONGTEXT)); dbms_output.put_line(length(V_LONGTEXT_TRIM));

-----Original Message-----
Sent: Wednesday, April 04, 2001 3:36 PM
To: Multiple recipients of list ORACLE-L

Thanks all of you for reply. I'm now use following to adjusted the logic:

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);

      CURSOR AKLangTextCur ( V_ENDDAT aktiv.ENDDAT%TYPE, V_ENDUHR

aktiv.ENDUHR%TYPE, V_AUSSAGEW aktiv.AUSSAGEW%TYPE, V_LANGTEXT aktiv.LANGTEXT%TYPE) IS

SELECT TO_CHAR(V_ENDDAT, 'YYYYMMDD')||' '|| V_ENDUHR||' '|| rtrim(V_AUSSAGEW)||' '|| rtrim(V_LANGTEXT) thisText

        FROM AKTIV, PROBLEM
        WHERE RTRIM(V_PROBLEM) = RTRIM(PROBLEM.PROBLEM)
        ORDER BY Aktiv_NR;

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 where RTRIM(V_PROBLEM) = RTRIM(PROBLEM.PROBLEM);

OPEN AKLangTextCur(V_ENDDAT, V_ENDUHR,V_AUSSAGEW, V_LANGTEXT); 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);

        UPDATE PROBBES
        SET LANGTEXT = V_LONGTEXT_TRIM
        WHERE PROBLEM = V_PROBLEM and
        rtrim(AUSSAGEW)= 'Report';  ...

But I'm getting 'ORA-06502: numeric or value error', and I'm not sure where it might come from.

I don't think it's from the trigger above.(the four concated fields are date, char, varchar2). It's more likely come from the application as I see. I have to check more...

Thanks again

Wendy

"Toepke, Kevin M" <ktoepke_at_cms.cendant.com> wrote:

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_N! ! ! R_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

--

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).

Do You Yahoo!?
Yahoo! Mail Personal <http://personal.mail.yahoo.com/?.refer=mailiyfoot> Address - Get email at your own domain with Yahoo! Mail.

--

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

Author: Bala, Prakash
INET: prakash.bala_at_cingular.com

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