A cursor definition always follows a declare, so its
just a case of nesting the appropriate
declare-begin-end where you like in your procedure
hth
connor
- Wendy Y <lannyue_at_yahoo.com> wrote: >
> 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 Personal Address - Get email at your own
> domain with Yahoo! Mail.
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Get your free @yahoo.co.uk address at
http://mail.yahoo.co.uk
or your free @yahoo.ie address at
http://mail.yahoo.ie
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
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 - 16:47:54 CDT
