Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> PL/SQL: Scope of Cursor FOR Loop Records
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
IS
BEGIN
UPDATE table1
SET ...
WHERE table1.key = cursor_record.key;
END;
PROCEDURE update_table2
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
ELSIF cursor_record.action_flag = '2' THEN
update_table2
ELSE
/* error logic */
END IF;
...
END LOOP;
END;
Received on Fri Dec 06 2002 - 10:14:24 CST
![]() |
![]() |