Re: PL/SQL help needed

From: <michael_bialik_at_my-deja.com>
Date: Sun, 25 Jul 1999 20:48:03 GMT
Message-ID: <7nft61$8q4$1@nnrp1.deja.com>

Hi.

 I think the previous messages already made clear what  is the problem.
 I would use ROWID instead of accessing the table through index :

CURSOR some_policies IS

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

 BEGIN
    FOR policy_number IN some_policies LOOP

      UPDATE policy p
         SET    non_renewal_reason = 'I'
         WHERE rowid = policy_number.rowid;
      COMMIT;

    END LOOP;
 END;  Good luck. Michael.

In article <379a574b.429212902_at_news.idworld.net>,   James-Garrison_at_excite.com wrote:
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Sun Jul 25 1999 - 15:48:03 CDT