Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> PL/SQL: Scope of Cursor FOR Loop Records

PL/SQL: Scope of Cursor FOR Loop Records

From: Ubiquitous <weberm_at_polaris.net>
Date: Fri, 6 Dec 2002 16:14:24 +0000 (UTC)
Message-ID: <asqid0$ko5$1@news.utelfla.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US