Re: PL/SQL help needed

Your updating every row twice with its own values. Your update does not reference the data from the cursor. something like below should work.

Declare

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;
R1 some_policies%rowtype;

BEGIN
OPEN SOME_POLICIES; LOOP
  FETCH C1 INTO R1;
  EXIT WHEN C1%NOTFOUND;
  UPDATE policy p
SET non_renewal_reason = 'I'
WHERE p.policy_number = R1. policy_number AND P.policy_date_time = R1.policy_date_time;

end loop;
commit;
end;

James Garrison wrote in message <379a574b.429212902_at_news.idworld.net>...
>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 - 20:46:43 CDT