Re: PL/SQL: Scope of Cursor FOR Loop Records

Ubiquitous wrote:
> I am experiencing a problem with using a local module which is
> declared within a cursor FOR loop and wondered if someone out
> there has had more experience with this sort of thing.
>
> Basically, I have a cursor FOR loop which performs updates
> a table depending on what it finds in one of the fields
> which is returned using an IF-ELSIF-ELSE statement (the ELSE
> is used to handle unrecognised field values. Rather than
> clutter my procedure with UPDATE statements, I replaced
> them local modules which are placed at the begining of the
> script and put calls to them within the IF-ELSIF-ELSE statement
> in the FOR loop. If I understand this PL/SQL book I have on-hand,
> I should be able to reference the implicit record variable
> because the local procedures are called from within the FOR
> loop (and are therefore, local). However, when I try to run
> it, it complains about them being undefined. Any ideas?
>
> Sample coding:
> CREATE OR REPLACE PROCEDURE assign_rcptnum
> IS
> /* varibles declared */
>
> /* cursor declared */
> CURSOR table_cur IS
> SELECT ...
> FROM ...
> WHERE... ;
>
> /*local modules */
> PROCEDURE update_table1

PROCEDURE update_table1 (nin_key in number)

> IS
> BEGIN
> UPDATE table1
> SET ...
> WHERE table1.key = cursor_record.key;
> END;
>
> PROCEDURE update_table2

PROCEDURE update_table2 (nin_key in number)

> IS
> BEGIN
> UPDATE table2
> SET ...
> WHERE table2.key = cursor_record.key;
> END;
>
>
> /* MAIN logic */
> BEGIN
> ...
>
> FOR cursor_record IN table_cur
> LOOP
> BEGIN
> ...
> IF cursor_record.action_flag = '1' THEN
> [A update_table1

        update_table1 (cursor_record.key)

> ELSIF cursor_record.action_flag = '2' THEN
> update_table2

        update_table2 (cursor_record.key)

> ELSE
> /* error logic */
> END IF;
> ...
> END LOOP;
> END;
>
>
>
>
>
>
>
Received on Fri Dec 06 2002 - 11:11:18 CST