Re: PL/SQL help needed

jrg_at_idworld.net (James Garrison) writes:

> 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.

James,

the reason for the problem is your UPDATE statement:

UPDATE policy p
SET non_renewal_reason = 'I'
WHERE p.policy_number = policy_number
AND P.policy_date_time = policy_date_time;

policy_number and policy_date_time are not values that you fetched from your cursor, they are the original columns in your table; so your are comparing each of these columns to itself. This is the reason why all records in your policy table qualify for the update.

To get this to work, you should make the following modifications:

FOR policy_rec IN some_policies LOOP

   UPDATE policy p
   SET non_renewal_reason = 'I'
   WHERE p.policy_number = policy_rec.policy_number    AND P.policy_date_time = policy_rec.policy_date_time; END LOOP; If you use a cursor FOR-Loop, this implicitly declares a record loop variable with has your cursor%ROWTYPE. To use the fetched values, you need to use dot-notation to access individual components of the record variable.

It's also a good idea to follow a specific naming convention: use _cur for cursor declarations and _rec for record variables. This will make the code clearer, and it's less prone to naming conflicts.

To address your second problem: you could include

DBMS_OUTPUT.PUT_LINE('Updating policy number: ' || policy_rec.policy_number);

just before the UPDATE statement. But you should remove it in production code, because this is likely to cause a DBMS_OUTPUT buffer overflow if many loop iterations are performed.

HTH,
Peter

--
Peter Schneider
pschneider_at_knuut.de Received on Sun Jul 25 1999 - 10:41:51 CDT