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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
