PL/SQL help needed

In my declare section I've defined a cursor. If I cut and paste the select statement into SQLPLUS it returns the correct number of rows. When I run my script opening the cursor with a FOR..IN..LOOP it returns every row in the table. I'm trying to use DBMS_OUTPUT.PUT_LINE statements to diagnose the error but am unable to get it to work correctly. Here's an example:

Delcare

CURSOR some_policies IS

		SELECT p.policy_number, p.policy_date_time
		FROM   policy p, register_cur rc
		WHERE  p.policy_number = rc.policy_number
		AND    p.renewal_code = 1
		AND    rc.status_1 = 5;

**********Running the above select statement from the sql> command
line correctly returns 2 rows*************

BEGIN

		FOR policy_number IN some_policies LOOP
			UPDATE policy p
			SET    non_renewal_reason = 'I'
			WHERE  p.policy_number = policy_number
			AND    P.policy_date_time = policy_date_time;
			COMMIT;

*********This updates every row in the table!************
                                

  DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || ' policies.');

**This gives a row count of every row in the table! It should be 2***

  DBMS_OUTPUT.PUT_LINE ('Policy number ' || v_policy_no || ' .');                         

******How can I map policy number into v_policy_no????*************
                        

                END LOOP;
END; I have two problems: 1. Why is every row getting returned when I open the cursor. 2. How can I display the policy numbers affected by the update.

TIA. Please help asap, let me know if you need more detail on the problem. The script is much longer than this snippet. Received on Sat Jul 24 1999 - 19:34:18 CDT